﻿Imports BusinessRule.DTO
Imports BusinessRule.IBusiness
Imports System.Text
Imports DatabaseAccess.DBUtility
Imports System.Data.OracleClient
Imports BusinessRule.Common
Imports Newtonsoft.Json

'Description:转工管理类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Wangjh
'create Date:2017-5-15
'Rewriter:
'Rewrite Date:
Public Class uLMSContainerShipBusiness
    Implements uLMSContainerShipIBusiness
    Private toAppID_WMS = System.Configuration.ConfigurationManager.AppSettings("ToAppID_WMS").ToString() '推送给数据中心目标地址（WMS）
    Private m_ConCurrentinfoObj As New uLMSContainercurrentinfoBusiness
    ''' <summary>
    ''' 获取查询结果
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks>REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME, modify by tianFW 2018年5月17日 单件单卡取C.TOGETHERFLAG 数量批取C.PRODUCTNAME</remarks>
    Protected Function GetContainerShipData(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSContainerShipIBusiness.GetContainerShipData
        Dim strSelect As String =
            <string>
             SELECT C.PRODUCTNAME,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, C.WORKFLOWID,c.workflowname||':'||c.workflowrevision workflowinfo,C.WORKFLOWSTEPSEQUENCE AS SEQUENCE,C.TOGETHERFLAG,
             C.CONTAINERTYPE,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,C.PLANQTY,
             C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,(nvl(C.workflowroute,C.productname)||'/'||c.containerno) ProductBatch, 
             (C.productname||'/'||c.containerno) ProductBatchHidden,
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strSelect)
        ' sb.AppendLine("REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME,")
        sb.AppendLine("SUM(C.QTY) AS WIPQTY ,'' CONTAINERID,''CONTAINERNAME,NULL WORKREPORTNUM,'' FaContianerID,''SHIPID")
        sb.AppendLine(" FROM CONTAINERCURRENTINFO C ")
        Dim leftJoin As String = <string>
                                    LEFT JOIN ProcessDealRecord PDL ON PDL.PARENTID=C.SHIPID and pdl.ProcessStatus='0' and (pdl.Processtype=1 or pdl.Processtype=4)
                                 </string>
        sb.AppendLine(leftJoin)
        Dim sbCondition As String = " where c.containerstatus=1"
        sbCondition += " AND C.SPLITNUM=0 AND C.ISINSTORE=0"
        If QueryCondition("Status") = "3" Or QueryCondition("Status") = "5" Or QueryCondition("Status") = "5A" Then '待转出或已转出
            sbCondition += String.Format(" and C.ORIGINALFACTORYID='{0}' ", QueryCondition("FactoryID"))
        Else
            sbCondition += String.Format(" and C.FACTORYID='{0}' ", QueryCondition("FactoryID"))
            'If QueryCondition("Status") <> "9" Then  
            '    sbCondition += " AND  C.isdispatch=0 "
            'End If
        End If

        sbCondition += GetCondition(QueryCondition)
        If QueryCondition.Keys.Contains("Status") Then
            If QueryCondition("Status") = "1" Then '待申请
                sbCondition += String.Format("    AND (C.WORKCENTERID='{0}' OR C.STEPWORKCENTERIDS LIKE '%{0}%') AND C.ORIGINALFACTORYID=C.SPECFACTORYID AND (SHIPNUM=0 OR SHIPNUM=8) AND C.Iscommissioning=0", QueryCondition("WorkCenterID"))
            ElseIf QueryCondition("Status") = "2" Then '已申请
                sbCondition += String.Format(" AND C.SHIPNUM=1 AND (C.WORKCENTERID='{0}' OR C.STEPWORKCENTERIDS LIKE '%{0}%')", QueryCondition("WorkCenterID"))
            ElseIf QueryCondition("Status") = "0" Then '待审核
                sbCondition += String.Format("  AND C.SHIPNUM=1 ")
            ElseIf QueryCondition("Status") = "4" Then '已审核
                sbCondition += String.Format("   AND C.SHIPNUM=2")
            ElseIf QueryCondition("Status") = "3" Then '待转出
                sbCondition += String.Format("   AND C.SHIPNUM=3")
            ElseIf QueryCondition("Status") = "5" Then '已转出
                sbCondition += String.Format("   AND C.SHIPNUM=4 ")
            ElseIf QueryCondition("Status") = "5A" Then '已转出且退回
                sbCondition += String.Format("   AND (C.SHIPNUM =4 AND pdl.parentid IS NOT NULL )")
            ElseIf QueryCondition("Status") = "8" Then '待接收
                sbCondition += String.Format("  AND (C.SHIPNUM=4 AND pdl.parentid IS NULL)")
            ElseIf QueryCondition("Status") = "9" Then '已接收
                sbCondition += String.Format("    AND (C.SHIPNUM=5 OR C.SHIPNUM=6) AND pdl.parentid IS NULL")
            End If
        End If
        sb.AppendLine(sbCondition)
        sb.AppendLine(" AND C.CONTAINERTYPE='7'") '单件
        sb.AppendLine(" GROUP BY C.PRODUCTID, C.PRODUCTNAME,c.workflowname,c.workflowrevision, C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID, C.WORKFLOWID, C.WORKFLOWSTEPSEQUENCE, C.SPECNO,") '将 C.SEQUENCE更换为C.WORKFLOWSTEPSEQUENCE tianFW 2017-06-03
        sb.AppendLine(" C.TOGETHERFLAG, C.CONTAINERTYPE,C.MFGORDERNAME,C.PLANQTY, C.PLANNEDSTARTDATE, C.PLANNEDCOMPLETIONDATE,")
        sb.AppendLine(" C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,c.containerno,C.workflowroute")
        sb.AppendLine(" UNION ")
        sb.AppendLine(strSelect)
        'sb.AppendLine("SUBSTR(C.CONTAINERNAME, LENGTH(C.PRODUCTNAME) + 2) TOGETHERFLAGNAME,")
        sb.AppendLine("C.QTY AS WIPQTY , C.CONTAINERID,C.CONTAINERNAME,C.WORKREPORTNUM,C.CONTAINERID AS FaContianerID,C.SHIPID")
        sb.AppendLine(" FROM CONTAINERCURRENTINFO C ")
        sb.AppendLine(leftJoin)
        sb.AppendLine(sbCondition)
        sb.AppendLine(" AND C.CONTAINERTYPE<>'7' ")
        sb.AppendLine(<string>
                    GROUP BY C.PRODUCTNAME,C.PRODUCTID,c.workflowname,c.workflowrevision,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE,C.TOGETHERFLAG,C.MFGORDERNAME,C.PLANQTY,
                    C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,
                    C.CONTAINERTYPE,c.containerno,C.QTY, C.CONTAINERID,C.CONTAINERNAME,C.WORKREPORTNUM,C.CONTAINERID,C.SHIPID,C.workflowroute</string>.Value)
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
        paraPage.strSQL = sb.ToString

        Dim selectSql As String = "SELECT "
        selectSql = selectSql & " C.* FROM CONTAINERCURRENTINFO C "
        selectSql += leftJoin
        sbCondition = String.Format(selectSql & " {0}", sbCondition)

        If QueryCondition.ContainsKey("NewGrid") Then
            Dim result As uMESProcessResult = DatabaseHelper.RunProcedureForPage(paraPage)
            Dim sonSql As DataTable = New DataTable
            sonSql.Columns.Add("ChildContainerSql")
            sonSql.Columns("ChildContainerSql").DefaultValue = sbCondition
            sonSql.Rows.Add()
            sonSql.AcceptChanges()

            result.DBTable.Merge(sonSql)
            result.DBTable.DefaultView.RowFilter = " ProductBatch is not null"
            result.DBTable = result.DBTable.DefaultView.ToTable.Copy
            Return result
        Else
            Return DatabaseHelper.GetContainerForPage(paraPage, sbCondition)
        End If
    End Function

    ''' <summary>
    ''' 查询单件单卡子卡的所需信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetChildContainerNo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSContainerShipIBusiness.GetChildContainerNo
        para("ChildContainerSql") += String.Format(" and((C.CONTAINERTYPE=7 AND C.TOGETHERFLAG='{0}' AND C.PRODUCTID='{1}'",
                                                  para("TogatherFlag"), para("ProductID"))

        If para.ContainsKey("WorkflowStepID") Then
            para("ChildContainerSql") += String.Format(" AND C.WORKFLOWSTEPID='{0}' ", para("WorkflowStepID"))
        ElseIf para.ContainsKey("WorkflowID") Then
            para("ChildContainerSql") += String.Format(" AND C.WorkflowID='{0}' ", para("WorkflowID"))
        End If

        para("ChildContainerSql") += String.Format(" AND C.MFGORDERNAME='{0}') OR (C.CONTAINERTYPE<>7 AND C.CONTAINERNAME='{1}') )", para("MfgOrderName"), para("ProductBatchHidden"))

        para("ChildContainerSql") += " ORDER BY C.CONTAINERNAME"
        Return DatabaseHelper.Query_ReturnDatTable(para("ChildContainerSql"))
    End Function

    ''' <summary>
    ''' 创建批次查询数据条件
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetCondition(ByVal para As Dictionary(Of String, String)) As String
        Dim sbCondition As New StringBuilder
        If para.Keys.Contains("ContainerName") Then
            sbCondition.AppendFormat(" AND C.CONTAINERNAME LIKE '%{0}%'", para("ContainerName"))
        End If
        If para.Keys.Contains("TeamID") Then
            sbCondition.AppendFormat(" AND C.TEAMID='{0}' ", para("TeamID"))
        End If
        If para.Keys.Contains("ProductName") Then
            sbCondition.AppendFormat(" AND C.PRODUCTNAME LIKE '%{0}%'", para("ProductName"))
        End If
        If para.Keys.Contains("StepNo") Then
            sbCondition.AppendFormat(" AND TO_NUMBER(C.SPECNO) =TO_NUMBER('{0}')", para("StepNo"))
        End If
        If para.Keys.Contains("MfgOrderName") Then 'add:Wangjh 2017/8/8
            sbCondition.AppendFormat(" AND C.MfgOrderName LIKE '%{0}%'", para("MfgOrderName"))
        End If
        GetCondition = sbCondition.ToString
    End Function
    ''' <summary>
    ''' 获取当前工序及之后工序的转工申请信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetSpecShipInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSContainerShipIBusiness.GetSpecShipInfo
        Dim strSql As New StringBuilder
        strSql.AppendLine("SELECT WS2.steptype,WS2.WORKFLOWSTEPID,WS2.WORKFLOWSTEPNAME,WS2.SEQUENCE,E.FULLNAME AS PROPOSERNAME,CSR.PREPOSERDATE,CSR.REPAIRQTY," &
                          " AE.EMPLOYEENAME AS AUDITORNAME,CSR.AUDITDATE,FA.FACTORYNAME,W.WORKFLOWID, C.CONTAINERID FROM WORKFLOWSTEP WS ")
        strSql.AppendLine("  LEFT JOIN WORKFLOW W ON WS.WORKFLOWID = W.WORKFLOWID")
        strSql.AppendLine("  LEFT JOIN workflowstep ws2 ON ws2.workflowid=w.workflowid")
        strSql.AppendLine(" LEFT JOIN WORKFLOWBASE WB ON WB.WORKFLOWBASEID=W.WORKFLOWBASEID")

        strSql.AppendLine("  INNER JOIN Currentstatus cu ON cu.workflowstepid=ws.workflowstepid	")
        strSql.AppendLine(" INNER JOIN CONTAINER C ON C.Currentstatusid=cu.currentstatusid")

        strSql.AppendFormat("LEFT JOIN CONTAINERSHIPRECORD CSR ON CSR.WORKFLOWSTEPID = WS2.WORKFLOWSTEPID AND CSR.CONTAINERID=C.CONTAINERID AND CSR.ISUSE=0")
        strSql.AppendLine(" LEFT JOIN EMPLOYEE E ON E.EMPLOYEEID = CSR.PROPOSERID")
        strSql.AppendLine(" LEFT JOIN EMPLOYEE AE ON AE.EMPLOYEEID = CSR.AUDITOR")
        strSql.AppendLine(" LEFT JOIN FACTORY FA ON FA.FACTORYID = CSR.TOFACTORYID")
        strSql.AppendFormat(" WHERE W.WORKFLOWID = '{0}' AND WS2.SEQUENCE >= '{1}' AND C.CONTAINERID='{2}'", para("WorkFlowID"), para("Sequence"), para("ContainerID"))
        strSql.AppendLine(" ORDER BY WS2.SEQUENCE")
        GetSpecShipInfo = DatabaseHelper.Query_ReturnDatTable(strSql.ToString)
    End Function
    ''' <summary>
    ''' 插入数据到转工信息V2.0
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function InsertContainerShip(ByVal para As Dictionary(Of String, String), ByRef containerShipRecordID As String) As Boolean Implements uLMSContainerShipIBusiness.InsertContainerShip

        Dim checkShipSql = String.Format("delete from containershiprecord csr WHERE csr.containerid='{0}' AND csr.workflowstepid='{1}' and isuse=0 and csr.senddate is null", para("ContainerID"), para("WorkFlowStepID"))
        DatabaseHelper.ExecuteSql(checkShipSql)

        Dim strRecordSql As String = " INSERT INTO CONTAINERSHIPRECORD(PROPOSERID,PREPOSERDATE,ISUSE,CONTAINERSHIPRECORDID,FACTORYID," &
                                    "CONTAINERID,WORKFLOWSTEPID,TOWORKFLOWSTEPID,ISTRANSFER)"
        strRecordSql += " VALUES(:PROPOSERID,SYSDATE,0,:CONTAINERSHIPRECORDID,:FACTORYID,:CONTAINERID,:WORKFLOWSTEPID,:TOWORKFLOWSTEPID,0)"

        Dim insertPara(5) As OracleParameter
        containerShipRecordID = Guid.NewGuid.ToString
        insertPara(0) = DatabaseHelper.NewParameter(":PROPOSERID", para("ProposerID"), OracleType.VarChar)
        insertPara(1) = DatabaseHelper.NewParameter(":CONTAINERSHIPRECORDID", containerShipRecordID, OracleType.VarChar)
        insertPara(2) = DatabaseHelper.NewParameter(":FACTORYID", para("FactoryID"), OracleType.VarChar)
        insertPara(3) = DatabaseHelper.NewParameter(":CONTAINERID", para("ContainerID"), OracleType.VarChar)
        insertPara(4) = DatabaseHelper.NewParameter(":WORKFLOWSTEPID", para("WorkFlowStepID"), OracleType.VarChar)
        insertPara(5) = DatabaseHelper.NewParameter(":TOWORKFLOWSTEPID", para("ToWorkFlowStepID"), OracleType.VarChar)
        Dim i As Integer = DatabaseHelper.ExecuteSql(strRecordSql, insertPara)
        If i > 0 Then
            InsertContainerShip = True
            '更新总表
            Dim updatePara As New Dictionary(Of String, String)
            Dim conditionPara As New Dictionary(Of String, String)
            conditionPara("ContainerID") = "'" + para("ContainerID") + "'"
            updatePara("shipid") = "'" + containerShipRecordID + "'"
            updatePara("shipnum") = 1
            updatePara("isship") = 1
            m_ConCurrentinfoObj.UpdateContainerCurrentInfoByField(updatePara, conditionPara)
            m_ConCurrentinfoObj.UpdateTransferOfTable("CONTAINERSHIPRECORD", "CONTAINERSHIPRECORDID", containerShipRecordID)
            '
            Exit Function
        End If
        InsertContainerShip = False
    End Function
    ''' <summary>
    ''' 插入数据到转工工序明细
    ''' </summary>
    ''' <param name="containerShipRecordID"></param>
    ''' <param name="workflowStepID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function InsertContainerShipStep(ByVal containerShipRecordID As String, ByVal workflowStepID As String) As Boolean Implements uLMSContainerShipIBusiness.InsertContainerShipStep

        Dim containerId As String = ""
        containerId = DatabaseHelper.GetSingle("select containerid from containershiprecord csr where csr.containershiprecordid='" + containerShipRecordID + "'")

        Dim strRecordStepSql = "INSERT INTO CONTAINERSHIPRECORDSTEP(CONTAINERSHIPRECORDID, WORKFLOWSTEPID,containerid) " &
           " VALUES (:CONTAINERSHIPRECORDID, :WORKFLOWSTEPID,'" + containerId + "')"
        Dim insertStepPara(1) As OracleParameter
        insertStepPara(0) = DatabaseHelper.NewParameter(":CONTAINERSHIPRECORDID", containerShipRecordID, OracleType.VarChar)
        insertStepPara(1) = DatabaseHelper.NewParameter(":WORKFLOWSTEPID", workflowStepID, OracleType.VarChar)
        Dim i As Integer = DatabaseHelper.ExecuteSql(strRecordStepSql, insertStepPara)
        If i > 0 Then
            InsertContainerShipStep = True
            Exit Function
        End If
        InsertContainerShipStep = False
    End Function
    ''' <summary>
    ''' 获取已转工申请的工序
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetAppliedSpecShip(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSContainerShipIBusiness.GetAppliedSpecShip
        Dim strSql As New StringBuilder
        strSql.AppendLine("SELECT PDL.*,A.* FROM (")
        strSql.AppendLine("SELECT C.CONTAINERNAME,WS.SEQUENCE,WS.WORKFLOWSTEPNAME,E.FULLNAME AS PROPOSERNAME,AE.FULLNAME AS AUDITORNAME,FA.FACTORYNAME," &
                          " CSR.PREPOSERDATE,CSR.AUDITDATE,CSR.CONTAINERSHIPRECORDID,CSRS.WORKFLOWSTEPID,CSR.PROPOSERID,CSR.NOTE,CSR.NEEDCOMPLETEDATE,CSR.SENDDATE,WC.WorkCenterName ")
        strSql.AppendLine(",CSR.TOACCEPTDATE,CSR.REPAIRQTY")
        strSql.AppendLine(" FROM CONTAINERSHIPRECORD CSR")
        strSql.AppendLine(" LEFT JOIN CONTAINERSHIPRECORDSTEP CSRS ON CSRS.CONTAINERSHIPRECORDID = CSR.CONTAINERSHIPRECORDID")
        strSql.AppendLine(" LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSRS.WORKFLOWSTEPID")
        strSql.AppendLine(" LEFT JOIN WORKFLOW W ON W.WORKFLOWID=WS.WORKFLOWID")
        strSql.AppendLine(" LEFT JOIN CONTAINER C ON C.CONTAINERID = CSR.CONTAINERID")
        strSql.AppendLine(" LEFT JOIN WORKCENTER WC ON WC.WORKCENTERID=C.WORKCENTERID")
        strSql.AppendLine(" LEFT JOIN EMPLOYEE E ON CSR.PROPOSERID = E.EMPLOYEEID")
        strSql.AppendLine(" LEFT JOIN EMPLOYEE AE ON AE.EMPLOYEEID = CSR.AUDITOR")
        strSql.AppendLine(" LEFT JOIN FACTORY FA ON FA.FACTORYID = CSR.TOFACTORYID")
        strSql.AppendFormat(" WHERE (CSR.Auditresult<>1 OR CSR.Auditresult IS  null) AND C.CONTAINERID IN '{0}' AND W.WORKFLOWID = '{1}' AND WS.SEQUENCE>={2} AND CSR.ISUSE=0",
                            para("ContainerID"), para("WorkFlowID"), para("Sequence"))
        If para("Status") = "2" Then '已申请 要同工区且该用户申请的
            strSql.AppendFormat(" AND C.WORKCENTERID = '{0}' AND CSR.PROPOSERID = '{1}'", para("WorkCenterID"), para("EmployeeID"))
            'ElseIf para("Status") = "4" Then '已审核
        End If
        If para("Status") = "3" Or para("Status") = "4" Then '待转出或已审核
            strSql.AppendLine(" AND CSR.SENDDATE IS NULL")
        End If
        If para("Status") = "8" Or para("Status") = "5" Then '待接收或已转出
            strSql.AppendLine(" AND CSR.TOACCEPTDATE IS NULL")
        End If
        If para("Status") = "9" Then '已接收
            strSql.AppendLine(" AND CSR.TOACCEPTDATE IS not NULL")
        End If
        strSql.AppendLine(" UNION ALL")
        '子工艺转工情况
        strSql.AppendLine("SELECT C.CONTAINERNAME,WS2.SEQUENCE,WS2.WORKFLOWSTEPNAME,E.FULLNAME AS PROPOSERNAME,AE.FULLNAME AS AUDITORNAME,FA.FACTORYNAME, CSR.PREPOSERDATE,CSR.AUDITDATE,CSR.CONTAINERSHIPRECORDID,WS2.WORKFLOWSTEPID,CSR.PROPOSERID,CSR.NOTE,CSR.NEEDCOMPLETEDATE,CSR.SENDDATE,WC.WorkCenterName")
        strSql.AppendLine(",CSR.TOACCEPTDATE,CSR.REPAIRQTY")
        strSql.AppendLine(" FROM CONTAINERSHIPRECORD CSR")
        strSql.AppendLine(" LEFT JOIN CONTAINERSHIPRECORDSTEP CSRS ON CSRS.CONTAINERSHIPRECORDID = CSR.CONTAINERSHIPRECORDID")
        strSql.AppendLine(" LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSR.WORKFLOWSTEPID") '主制工艺工序
        strSql.AppendLine(" LEFT JOIN WORKFLOW W ON W.WORKFLOWID=WS.WORKFLOWID") '-主制工艺
        strSql.AppendLine(" LEFT JOIN WORKFLOWBASE WB ON WB.WORKFLOWBASEID=W.WORKFLOWBASEID") '--主制工艺
        strSql.AppendLine(" LEFT JOIN workflowSTEP WS2 ON WS2.WORKFLOWID=W.WORKFLOWID AND WS2.WORKFLOWSTEPID=CSRS.WORKFLOWSTEPID AND WS2.SEQUENCE>=WS.SEQUENCE") '子工艺之后的所有工序
        strSql.AppendLine(" LEFT JOIN WORKFLOWBASE CUWB ON CUWB.WORKFLOWBASEID=WS.SUBWORKFLOWBASEID")
        strSql.AppendLine(" LEFT JOIN WORKFLOW CUW ON CUW.WORKFLOWID=NVL(CUWB.REVOFRCDID,WS2.SUBWORKFLOWID)") '--当前工艺
        strSql.AppendLine(" LEFT JOIN CONTAINER C ON C.CONTAINERID = CSR.CONTAINERID")
        strSql.AppendLine(" LEFT JOIN WORKCENTER WC ON WC.WORKCENTERID=C.WORKCENTERID")
        strSql.AppendLine(" LEFT JOIN EMPLOYEE E ON CSR.PROPOSERID = E.EMPLOYEEID")
        strSql.AppendLine(" LEFT JOIN EMPLOYEE AE ON AE.EMPLOYEEID = CSR.AUDITOR")
        strSql.AppendLine(" LEFT JOIN FACTORY FA ON FA.FACTORYID = CSR.TOFACTORYID")
        strSql.AppendFormat(" WHERE (CSR.Auditresult<>1 OR CSR.Auditresult IS  null) AND C.CONTAINERID IN '{0}' AND CUW.WORKFLOWID = '{1}'  AND CSR.ISUSE=0",
                            para("ContainerID"), para("WorkFlowID"))
        If para("Status") = "3" Or para("Status") = "4" Then '待转出或已审核
            strSql.AppendLine(" AND CSR.SENDDATE IS NULL")
        End If
        If para("Status") = "8" Or para("Status") = "5" Then '待接收或已转出
            strSql.AppendLine(" AND CSR.TOACCEPTDATE IS NULL")
        End If
        If para("Status") = "9" Then '已接收
            strSql.AppendLine(" AND CSR.TOACCEPTDATE IS not NULL")
        End If
        strSql.AppendLine(") A ")
        strSql.AppendLine(" LEFT JOIN ProcessDealRecord PDL ON PDL.PARENTID=A.CONTAINERSHIPRECORDID and pdl.ProcessStatus='0'")

        strSql.AppendLine(" ORDER BY A.SEQUENCE")
        GetAppliedSpecShip = DatabaseHelper.Query_ReturnDatTable(strSql.ToString)
    End Function
    ''' <summary>
    ''' 删除已转工申请的工序V2.0
    ''' </summary>
    ''' <param name="ContainerShipRecordID"></param>
    ''' <param name="WorkflowStepID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function DelAppliedSpecShip(ByVal containerShipRecordID As String, ByVal workflowStepID As String) As Boolean Implements uLMSContainerShipIBusiness.DelAppliedSpecShip
        Dim strSql As String = "DELETE CONTAINERSHIPRECORDSTEP CSRS WHERE  CSRS.CONTAINERSHIPRECORDID=:CONTAINERSHIPRECORDID AND CSRS.WORKFLOWSTEPID=:WORKFLOWSTEPID"

        Dim delStepPara(1) As OracleParameter
        delStepPara(0) = DatabaseHelper.NewParameter(":CONTAINERSHIPRECORDID", containerShipRecordID, OracleType.VarChar)
        delStepPara(1) = DatabaseHelper.NewParameter(":WORKFLOWSTEPID", workflowStepID, OracleType.VarChar)
        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql, delStepPara)
        If i > 0 Then
            DelAppliedSpecShip = True
            Exit Function
        End If
        DelAppliedSpecShip = False

    End Function
    ''' <summary>
    ''' 按唯一ID删除转工申请V2.0
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function DelAppiedShip(ByVal containerShipRecordID As String) As Boolean Implements uLMSContainerShipIBusiness.DelAppiedShip
        Dim strSql As String = " UPDATE CONTAINERSHIPRECORD CSR SET ISUSE=1 WHERE CSR.CONTAINERSHIPRECORDID = :CONTAINERSHIPRECORDID"
        Dim delStepPara(0) As OracleParameter
        delStepPara(0) = DatabaseHelper.NewParameter(":CONTAINERSHIPRECORDID", containerShipRecordID, OracleType.VarChar)
        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql, delStepPara)
        If i > 0 Then
            DelAppiedShip = True
            '删除转工详细记录
            strSql = " begin update containershiprecive set ISUSE=1 where CONTAINERSHIPRECORDID='{0}';delete CONTAINERSHIPRECORDSTEP CSDD where csdd.Containershiprecordid='{0}'; end;"
            DatabaseHelper.ExecuteSql(String.Format(strSql, containerShipRecordID))
            '删除自动报工的记录 add:Wangjh 20220608
            strSql = <string>select csw.containerstepworkreportid from containerstepworkreport csw 
left join containercurrentinfo cci on cci.containerid=csw.containerid and cci.workflowstepid=csw.workflowstepid
left join containercheckinfo cc on cc.containerstepworkreportid=csw.containerstepworkreportid
where cci.shipid='{0}' and csw.isuse=0  and cc.containercheckinfoid is null and csw.containerfinishedcheckinfoid is null </string>.Value
            Dim workreportId = DatabaseHelper.GetSingle(String.Format(strSql, containerShipRecordID))
            If Not String.IsNullOrWhiteSpace(workreportId) Then
                strSql = <string>begin
UPDATE CONTAINERSTEPWORKREPORT CSW SET CSW.ISUSE=1,CSW.ISTRANSFER=0 WHERE CSW.CONTAINERSTEPWORKREPORTID='{0}';
update containercurrentinfo cci set cci.firstchecknum=0,cci.stepchecknum=0,cci.workreportnum=0 where cci.shipid='{1}';
end;</string>.Value
                strSql = String.Format(strSql, workreportId.ToString, containerShipRecordID)
                DatabaseHelper.ExecuteSql(strSql)
            End If
            '

            '更新总表一些信息
            Dim updatePara As New Dictionary(Of String, String)
            Dim conditionPara As New Dictionary(Of String, String)
            conditionPara("shipid") = "'" + containerShipRecordID + "'"
            updatePara("shipid") = "''"
            updatePara("shipnum") = 0
            updatePara("isship") = 0
            updatePara("FACTORYID") = "ORIGINALFACTORYID"
            '获取开卡分厂名字
            strSql = String.Format(<string>SELECT fa.factoryname  FROM containercurrentinfo cci 
                            LEFT JOIN Factory fa ON fa.factoryid=cci.originalfactoryid
                            WHERE cci.shipid='{0}'</string>.Value, containerShipRecordID)
            Dim temDt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
            If temDt.Rows.Count = 0 Then
                Exit Function
            End If
            '
            updatePara("FACTORYNAME") = "'" + temDt.Rows(0)("FACTORYNAME").ToString + "'"
            updatePara("WORKCENTERID") = "FROMWORKCENTERID"
            updatePara("WORKCENTERNAME") = "FROMWORKCENTERNAME"
            m_ConCurrentinfoObj.UpdateContainerCurrentInfoByField(updatePara, conditionPara)
            ' m_ConCurrentinfoObj.UpdateTransferOfTable("CONTAINERSHIPRECORD", "CONTAINERSHIPRECORDID", containerShipRecordID)
            Exit Function
        End If
        DelAppiedShip = False
    End Function
    ''' <summary>
    ''' 添加审核数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function UpdateAppiedShip(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSContainerShipIBusiness.UpdateAppiedShip
        Dim strSql As New StringBuilder
        strSql.AppendLine(" UPDATE CONTAINERSHIPRECORD CSR SET AUDITDATE = SYSDATE,AUDITOR= :AUDITOR, AUDITRESULT = :AUDITRESULT,TOFACTORYID = :TOFACTORYID,ISTRANSFER=0")
        strSql.AppendLine(" WHERE CSR.CONTAINERSHIPRECORDID=:CONTAINERSHIPRECORDID ")
        Dim updatePara(3) As OracleParameter
        updatePara(0) = DatabaseHelper.NewParameter(":AUDITOR", para("EmployeeID"), OracleType.VarChar)
        updatePara(1) = DatabaseHelper.NewParameter(":AUDITRESULT", para("AuditResult"), OracleType.Number)
        updatePara(2) = DatabaseHelper.NewParameter(":TOFACTORYID", para("FactoryID"), OracleType.VarChar)
        updatePara(3) = DatabaseHelper.NewParameter(":CONTAINERSHIPRECORDID", para("ContainerShipRecordID"), OracleType.VarChar)

        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql.ToString, updatePara)
        If i > 0 Then
            UpdateAppiedShip = True
            '更新总表一些信息
            m_ConCurrentinfoObj.UpdateContainerShip(para("ContainerID"))
            '
            Exit Function
        End If
        UpdateAppiedShip = False
    End Function
    ''' <summary>
    ''' 更新转出数据
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function UpdateAppiedShipSender(ByVal para As Dictionary(Of String, Object)) As Boolean Implements uLMSContainerShipIBusiness.UpdateAppiedShipSender
        Dim strSql As String = " UPDATE CONTAINERSHIPRECORD CSR SET ISTRANSFER=0,SENDER = :SENDER,Note=:Note,NEEDCOMPLETEDATE=TO_DATE(:NEEDCOMPLETEDATE, 'yyyy-mm-dd hh24:mi:ss'),SENDDATE=TO_DATE(:SENDDATE, 'yyyy-mm-dd hh24:mi:ss'),DELIVERYLOCATION=:DELIVERYLOCATION"
        Dim i As Integer = 0
        If para.ContainsKey("RepairQty") Then
            Dim updatePara(6) As OracleParameter
            strSql += ",REPAIRQTY=:RepairQty"
            strSql += " WHERE CSR.CONTAINERSHIPRECORDID=:CONTAINERSHIPRECORDID"
            updatePara(0) = DatabaseHelper.NewParameter(":SENDER", para("Sender"), OracleType.VarChar)
            updatePara(1) = DatabaseHelper.NewParameter(":NEEDCOMPLETEDATE", para("NeedCompleteDate"), OracleType.VarChar)
            updatePara(2) = DatabaseHelper.NewParameter(":CONTAINERSHIPRECORDID", para("ContainerShipRecordID"), OracleType.VarChar)
            updatePara(3) = DatabaseHelper.NewParameter(":SENDDATE", para("SendDate"), OracleType.VarChar)
            updatePara(4) = DatabaseHelper.NewParameter(":Note", para("Note"), OracleType.VarChar)
            updatePara(5) = DatabaseHelper.NewParameter(":DELIVERYLOCATION", para("DeliveryLocation"), OracleType.VarChar)
            updatePara(6) = DatabaseHelper.NewParameter(":RepairQty", para("RepairQty"), OracleType.VarChar)
            i = DatabaseHelper.ExecuteSql(strSql.ToString, updatePara)
        Else
            Dim updatePara(5) As OracleParameter
            strSql += " WHERE CSR.CONTAINERSHIPRECORDID=:CONTAINERSHIPRECORDID"
            updatePara(0) = DatabaseHelper.NewParameter(":SENDER", para("Sender"), OracleType.VarChar)
            updatePara(1) = DatabaseHelper.NewParameter(":NEEDCOMPLETEDATE", para("NeedCompleteDate"), OracleType.VarChar)
            updatePara(2) = DatabaseHelper.NewParameter(":CONTAINERSHIPRECORDID", para("ContainerShipRecordID"), OracleType.VarChar)
            updatePara(3) = DatabaseHelper.NewParameter(":SENDDATE", para("SendDate"), OracleType.VarChar)
            updatePara(4) = DatabaseHelper.NewParameter(":Note", para("Note"), OracleType.VarChar)
            updatePara(5) = DatabaseHelper.NewParameter(":DELIVERYLOCATION", para("DeliveryLocation"), OracleType.VarChar)
            i = DatabaseHelper.ExecuteSql(strSql.ToString, updatePara)
        End If

        If i > 0 Then
            UpdateAppiedShipSender = True
            '更新总表一些信息
            m_ConCurrentinfoObj.UpdateContainerShip(para("ContainerID"))
            '
            Exit Function
        End If
        UpdateAppiedShipSender = False
    End Function
    ''' <summary>
    ''' 更新接收数据到转工记录表
    ''' </summary>
    ''' <param name="ID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function UpdateShipRecordAccept(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSContainerShipIBusiness.UpdateShipRecordAccept
        Dim strSql As String = "UPDATE CONTAINERSHIPRECORD C SET TOACCEPTDATE = SYSDATE,TOACCEPTOR = :TOACCEPTOR," &
                                "TOWORKCENTERID = :TOWORKCENTERID,estimatedcompletedate=to_date(:estimatedcompletedate,'yyyy-mm-dd hh24:mi:ss'),ISTRANSFER=0  WHERE C.CONTAINERSHIPRECORDID = :CONTAINERSHIPRECORDID"
        Dim updatePara(3) As OracleParameter
        updatePara(0) = DatabaseHelper.NewParameter(":CONTAINERSHIPRECORDID", para("ContainerShipRecordID"), OracleType.VarChar)
        updatePara(1) = DatabaseHelper.NewParameter(":TOACCEPTOR", para("ToAcceptor"), OracleType.VarChar)
        updatePara(2) = DatabaseHelper.NewParameter(":TOWORKCENTERID", para("ToWorkCenterID"), OracleType.VarChar)
        updatePara(3) = DatabaseHelper.NewParameter(":estimatedcompletedate", para("EstimatedCompleteDate"), OracleType.VarChar)
        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql.ToString, updatePara)
        If i > 0 Then
            UpdateShipRecordAccept = True
            '更新总表一些信息
            m_ConCurrentinfoObj.UpdateContainerShip(para("ContainerID"))
            '
            Exit Function
        End If
        UpdateShipRecordAccept = False
    End Function
    ''' <summary>
    ''' 获取加工完成后的转工批次
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetContainerShipFinished(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSContainerShipIBusiness.GetContainerShipFinished
        Dim strSelect As String =
            <string>
             SELECT C.PRODUCTNAME,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE AS SEQUENCE, 
             C.TOGETHERFLAG,C.CONTAINERTYPE,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,C.PLANQTY,
             C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,c.workflowroute,c.containerno,            
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strSelect)
        ' sb.AppendLine("REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME,")
        sb.AppendLine("SUM(C.QTY) AS WIPQTY ,'' CONTAINERID,''CONTAINERNAME,NULL WORKREPORTNUM,'' FaContianerID,null splitfromid")
        sb.AppendLine(" FROM CONTAINERCURRENTINFO C ")

        sb.AppendLine("  LEFT JOIN containershiprecord csr ON csr.containershiprecordid=c.shipid")
        sb.AppendLine("  LEFT JOIN Userattribute ut ON ut.parentid=csr.tofactoryid AND ut.userattributename='转工转入直接完成'") '此配置项判断放在转工分厂

        Dim leftJoin As String = <string>

                                 </string>
        sb.AppendLine(leftJoin)
        Dim sbCondition As String = " where c.containerstatus=1"
        If QueryCondition("Status") = "7" Then '已完成
            sbCondition += String.Format(" and C.FACTORYID='{0}' ", QueryCondition("FactoryID"))
        ElseIf QueryCondition("Status") = "6" Or QueryCondition("Status") = "6A" Then '待完成
            sbCondition += String.Format(" and C.FACTORYID='{0}' ", QueryCondition("FactoryID"))
        ElseIf QueryCondition("Status") = "10" Or QueryCondition("Status") = "11" Then '待转入或已转入 Or QueryCondition("Status") = "10A" 
            sbCondition += String.Format(" and C.ORIGINALFACTORYID='{0}' ", QueryCondition("FactoryID"))
        End If
        sbCondition += GetCondition(QueryCondition)
        sbCondition += " AND C.SPLITNUM=0 AND C.ISINSTORE=0 "
        If QueryCondition("Status") <> "6A" And QueryCondition("Status") <> "10" Then 'And QueryCondition("Status") <> "10A" 
            sbCondition += " AND C.STEPCHECKNUM=0 AND C.FIRSTCHECKNUM in (0,2) " 'AND ( C.ISDISPATCH=0 OR (C.ISDISPATCH=1 AND C.WORKREPORTNUM>=C.QTY)) UPDATE:Wangjh 20220425 取消此条件
        End If
        If QueryCondition.Keys.Contains("Status") Then
            If QueryCondition("Status") = "6" Then '待完成
                sbCondition += " AND C.SHIPNUM=6 "
            ElseIf QueryCondition("Status") = "6A" Then '待完成 直接完成
                sbCondition += " AND (C.SHIPNUM=5 OR C.SHIPNUM=6)"
            ElseIf QueryCondition("Status") = "7" Then '已完成
                sbCondition += " AND C.SHIPNUM=7"
            ElseIf QueryCondition("Status") = "10" Then '待转入
                sbCondition += " AND (C.SHIPNUM=7 or (C.SHIPNUM=4 AND ut.attributevalue='是' ))" '直接转入判断放在转工分厂
                'ElseIf QueryCondition("Status") = "10A" Then '待转入 直接转入
                '    sbCondition += " AND (C.SHIPNUM=4 OR C.SHIPNUM=7)"
            ElseIf QueryCondition("Status") = "11" Then '已转入
                sbCondition += " AND C.SHIPNUM=8"
            End If
        End If
        sb.AppendLine(sbCondition)
        sb.AppendLine(" AND C.CONTAINERTYPE='7'")
        sb.AppendLine(" GROUP BY C.PRODUCTID, C.PRODUCTNAME, C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID, C.WORKFLOWID, C.WORKFLOWSTEPSEQUENCE, C.SPECNO,") '将 C.SEQUENCE更换为C.WORKFLOWSTEPSEQUENCE tianFW 2017-06-03
        sb.AppendLine(" C.TOGETHERFLAG, C.CONTAINERTYPE,C.MFGORDERNAME,C.PLANQTY, C.PLANNEDSTARTDATE, C.PLANNEDCOMPLETIONDATE,")
        sb.AppendLine(" C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,c.containerno,C.workflowroute")
        sb.AppendLine(" UNION ")
        sb.AppendLine(strSelect)
        ' sb.AppendLine("SUBSTR(C.CONTAINERNAME, LENGTH(C.PRODUCTNAME) + 2) TOGETHERFLAGNAME,")
        sb.AppendLine("C.QTY AS WIPQTY , C.CONTAINERID,C.CONTAINERNAME,C.WORKREPORTNUM,C.CONTAINERID AS FaContianerID,C.splitfromid")
        sb.AppendLine(" FROM CONTAINERCURRENTINFO C ")

        sb.AppendLine(" LEFT JOIN containershiprecord csr ON csr.containershiprecordid=c.shipid")
        sb.AppendLine(" LEFT JOIN Userattribute ut ON ut.parentid=csr.tofactoryid AND ut.userattributename='转工转入直接完成'") '此配置项判断放在转工分厂
        sb.AppendLine(leftJoin)
        sb.AppendLine(sbCondition)
        sb.AppendLine(" AND C.CONTAINERTYPE<>'7' ")

        sb.AppendLine(" ) a")
        sb.Insert(0, "select (nvl(a.workflowroute,a.productname)||'/'||a.containerno) ProductBatch,a.* from (")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
        paraPage.strSQL = sb.ToString

        sbCondition = String.Format(" SELECT C.* FROM CONTAINERCURRENTINFO C " &
                                    " LEFT JOIN containershiprecord csr ON csr.containershiprecordid=c.shipid" &
                                    " LEFT JOIN Userattribute ut ON ut.parentid=csr.tofactoryid AND ut.userattributename='转工转入直接完成'" &
                                   " {0} {1}", leftJoin, sbCondition)
        If QueryCondition.ContainsKey("NewGrid") Then
            Dim result As uMESProcessResult = DatabaseHelper.RunProcedureForPage(paraPage)
            Dim sonSql As DataTable = New DataTable
            sonSql.Columns.Add("ChildContainerSql")
            sonSql.Columns("ChildContainerSql").DefaultValue = sbCondition
            sonSql.Rows.Add()
            sonSql.AcceptChanges()

            result.DBTable.Merge(sonSql)
            result.DBTable.DefaultView.RowFilter = " ProductBatch is not null"
            result.DBTable = result.DBTable.DefaultView.ToTable.Copy
            Return result
        Else
            Return DatabaseHelper.GetContainerForPage(paraPage, sbCondition)
        End If

    End Function

    ''' <summary>
    ''' 加载选中流水卡的转工信息
    ''' add:Wangjh 20220322
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function LoadContainerShipInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSContainerShipIBusiness.LoadContainerShipInfo
        Dim strSql As String = " SELECT C.CONTAINERNAME, C.CONTAINERID,(CASE WHEN c.containertype=3 THEN 1 ELSE C.QTY END) QTY, FA.FACTORYNAME, CSR.FACTORYID,CSR.CONTAINERSHIPRECORDID,ws.sequence,w.workflowid," &
                                           "  wb.WorkFlowName,w.WorkFlowRevision,(CASE WHEN c.containertype=3 THEN cc.containername ELSE NULL END) childContainername,(CASE WHEN c.containertype=3 THEN cc.containerid ELSE NULL END) childcontainerid," &
                                           "csre.acceptanceqty,csre.lossqty,csre.defectqty,csre.reworkqty,CSRE.NONCONFORMINGNO,CSRE.SCRAPNO,WC.WORKCENTERNAME,CSR.Toworkflowstepid,WB2.Workflowname MAINWORKFLOWNAME,W2.WORKFLOWREVISION MAINWORKFLOWREVISION,W2.workflowid MAINworkflowid," &
                                           " TOWS.workflowstepname toworkflowstepname,TOWS.Steptype TOSteptype,DECODE(CSRE.CONTAINERSHIPRECORDID,NULL,0,1) ISCOMPLETECONFIRM,C.QTY2,U.UOMNAME,tows.sequence fasequence," &
                                           " csre.completedate,csr.Toacceptdate,csr.Needcompletedate,csr.EstimatedCompleteDate,csr.senddate,csre.containerid containerid2 FROM CONTAINER C" &
                                 " LEFT JOIN UOM U ON U.UOMID=C.UOM2ID" &
                                " LEFT JOIN CONTAINERSHIPRECORD CSR ON C.CONTAINERID = CSR.CONTAINERID AND CSR.ISUSE=0" &
                                "  LEFT JOIN WORKFLOWSTEP TOWS ON TOWS.WORKFLOWSTEPID=CSR.TOWORKFLOWSTEPID" &
                                " LEFT JOIN WORKFLOWSTEP FWS ON FWS.WORKFLOWSTEPID=CSR.WORKFLOWSTEPID" &
                                 " LEFT JOIN Currentstatus cu ON cu.currentstatusid=c.currentstatusid" &
                                "  LEFT JOIN workflowstep ws ON ws.workflowstepid=cu.workflowstepid" &
                                "  LEFT JOIN workflow w ON w.workflowid=ws.workflowid" &
                                " LEFT JOIN WORKFLOWBASE WB ON WB.WORKFLOWBASEID=W.WORKFLOWBASEID" &
                                " LEFT JOIN SUBWORKFLOWCHECKINFO SWC ON SWC.SUBWORKFLOWID=W.WORKFLOWID AND SWC.CONTAINERID=C.CONTAINERID" &
                                " LEFT JOIN WORKFLOW W2 ON W2.workflowid=TOWS.workflowid" &
                                " LEFT JOIN WORKFLOWBASE WB2 ON WB2.WORKFLOWBASEID=W2.WORKFLOWBASEID" &
                                " LEFT JOIN FACTORY FA ON FA.FACTORYID = CSR.FACTORYID" &
                                " LEFT JOIN CONTAINERSHIPRECIVE CSRE ON CSRE.CONTAINERSHIPRECORDID=CSR.CONTAINERSHIPRECORDID  AND CSRE.ISUSE=0" &
                                " LEFT JOIN WORKCENTER WC ON WC.WORKCENTERID=C.WORKCENTERID" &
                                " LEFT JOIN (SELECT CONTAINERNAME,containerid,parentcontainerid FROM container WHERE status=1  ) cc ON cc.parentcontainerid=c.containerid" &
                                " LEFT JOIN containershiprecive csre2 ON csre2.containershiprecordid=csr.containershiprecordid AND csre2.containerid=csr.containerid AND CSRE2.ISUSE=0" &
                                " LEFT JOIN Userattribute ut ON ut.parentid=csr.tofactoryid AND ut.userattributename='转工转入直接完成'" &
                                " WHERE 1=1 "
        Dim status As String = para("Status")

        If para.ContainsKey("WorkflowStepID") Then
            If Not String.IsNullOrWhiteSpace(para("WorkflowStepID")) Then
                strSql += String.Format(" and cu.WorkflowStepID='{0}' ", para("WorkflowStepID"))
            End If
        End If

        If para.ContainsKey("ContainerID") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerID")) Then
                strSql += String.Format(" and c.ContainerID='{0}' ", para("ContainerID"))
            End If
        End If

        If para.ContainsKey("ContainerType") Then
            If para("ContainerType") = "7" Then
                strSql += String.Format(" and c.togetherflag='{0}' ", para("TogetherFlag"))
            Else
                strSql += String.Format(" and c.containername='{0}' ", para("ProductBatchHidden"))
            End If
        End If


        If status = "6" Then
            strSql += "  AND CSRE2.Employeeid IS  NULL"
        End If
        If status = "7" Or status = "10" Then
            strSql += " AND ((CSRE2.EMPLOYEEID IS NOT NULL AND CSRE2.RECIVER IS NULL) OR (CSRE2.RECIVER IS NULL AND ut.attributevalue='是'))"
        End If
        If status = "10A" Then
            strSql += " AND CSRE2.RECIVER IS NULL"
        End If
        If status = "11" Then
            strSql += "  AND CSRE2.RECIVER IS NOT NULL"
        End If

        strSql += " GROUP BY C.CONTAINERNAME, C.CONTAINERID,C.CONTAINERTYPE,C.QTY, FA.FACTORYNAME, CSR.FACTORYID,CSR.CONTAINERSHIPRECORDID,ws.sequence,w.workflowid,  wb.WorkFlowName,w.WorkFlowRevision,CC.CONTAINERNAME,CC.CONTAINERID,csre.acceptanceqty,csre.lossqty,csre.defectqty,csre.reworkqty,CSRE.NONCONFORMINGNO,CSRE.SCRAPNO,WC.WORKCENTERNAME,CSR.Toworkflowstepid,WB2.Workflowname ,W2.WORKFLOWREVISION , TOWS.workflowstepname ,TOWS.Steptype ,CSRE.CONTAINERSHIPRECORDID,C.QTY2,U.UOMNAME,tows.sequence,csre.completedate,csr.Toacceptdate,csr.Needcompletedate,csr.senddate,csr.EstimatedCompleteDate,csr.senddate,W2.workflowid,csre.containerid"
        strSql += " order by csr.senddate desc"
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)

        Return dt

    End Function


    ''' <summary>
    ''' 加载选中流水卡的转工信息
    ''' </summary>
    ''' <param name="ID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function LoadContainerShipInfo(ByVal ID As String, ByVal status As String) As DataTable Implements uLMSContainerShipIBusiness.LoadContainerShipInfo
        Dim strSql As String = String.Format(" SELECT C.CONTAINERNAME, C.CONTAINERID,(CASE WHEN c.containertype=3 THEN 1 ELSE C.QTY END) QTY, FA.FACTORYNAME, CSR.FACTORYID,CSR.CONTAINERSHIPRECORDID,ws.sequence,w.workflowid," &
                                           "  wb.WorkFlowName,w.WorkFlowRevision,(CASE WHEN c.containertype=3 THEN cc.containername ELSE NULL END) childContainername,(CASE WHEN c.containertype=3 THEN cc.containerid ELSE NULL END) childcontainerid," &
                                           "csre.acceptanceqty,csre.lossqty,csre.defectqty,csre.reworkqty,CSRE.NONCONFORMINGNO,CSRE.SCRAPNO,WC.WORKCENTERNAME,CSR.Toworkflowstepid,WB2.Workflowname MAINWORKFLOWNAME,W2.WORKFLOWREVISION MAINWORKFLOWREVISION,W2.workflowid MAINworkflowid," &
                                           " TOWS.workflowstepname toworkflowstepname,TOWS.Steptype TOSteptype,DECODE(CSRE.CONTAINERSHIPRECORDID,NULL,0,1) ISCOMPLETECONFIRM,C.QTY2,U.UOMNAME,tows.sequence fasequence," &
                                           " csre.completedate,csr.Toacceptdate,csr.Needcompletedate,csr.EstimatedCompleteDate,csr.senddate,csre.containerid containerid2 FROM CONTAINER C" &
                                 " LEFT JOIN UOM U ON U.UOMID=C.UOM2ID" &
                                " LEFT JOIN CONTAINERSHIPRECORD CSR ON C.CONTAINERID = CSR.CONTAINERID AND CSR.ISUSE=0" &
                                "  LEFT JOIN WORKFLOWSTEP TOWS ON TOWS.WORKFLOWSTEPID=CSR.TOWORKFLOWSTEPID" &
                                " LEFT JOIN WORKFLOWSTEP FWS ON FWS.WORKFLOWSTEPID=CSR.WORKFLOWSTEPID" &
                                 " LEFT JOIN Currentstatus cu ON cu.currentstatusid=c.currentstatusid" &
                                "  LEFT JOIN workflowstep ws ON ws.workflowstepid=cu.workflowstepid" &
                                "  LEFT JOIN workflow w ON w.workflowid=ws.workflowid" &
                                " LEFT JOIN WORKFLOWBASE WB ON WB.WORKFLOWBASEID=W.WORKFLOWBASEID" &
                                " LEFT JOIN SUBWORKFLOWCHECKINFO SWC ON SWC.SUBWORKFLOWID=W.WORKFLOWID AND SWC.CONTAINERID=C.CONTAINERID" &
                                " LEFT JOIN WORKFLOW W2 ON W2.workflowid=TOWS.workflowid" &
                                " LEFT JOIN WORKFLOWBASE WB2 ON WB2.WORKFLOWBASEID=W2.WORKFLOWBASEID" &
                                " LEFT JOIN FACTORY FA ON FA.FACTORYID = CSR.FACTORYID" &
                                " LEFT JOIN CONTAINERSHIPRECIVE CSRE ON CSRE.CONTAINERSHIPRECORDID=CSR.CONTAINERSHIPRECORDID  AND CSRE.ISUSE=0" &
                                " LEFT JOIN WORKCENTER WC ON WC.WORKCENTERID=C.WORKCENTERID" &
                                " LEFT JOIN (SELECT CONTAINERNAME,containerid,parentcontainerid FROM container WHERE status=1  ) cc ON cc.parentcontainerid=c.containerid" &
                                " LEFT JOIN containershiprecive csre2 ON csre2.containershiprecordid=csr.containershiprecordid AND csre2.containerid=csr.containerid AND CSRE2.ISUSE=0" &
                                " LEFT JOIN Userattribute ut ON ut.parentid=csr.tofactoryid AND ut.userattributename='转工转入直接完成'" &
                                " WHERE CSR.CONTAINERID = '{0}' ", ID)
        If status = "6" Then
            strSql += "  AND CSRE2.Employeeid IS  NULL"
        End If
        If status = "7" Or status = "10" Then
            strSql += " AND ((CSRE2.EMPLOYEEID IS NOT NULL AND CSRE2.RECIVER IS NULL) OR (CSRE2.RECIVER IS NULL AND ut.attributevalue='是'))"
        End If
        If status = "10A" Then
            strSql += " AND CSRE2.RECIVER IS NULL"
        End If
        If status = "11" Then
            strSql += "  AND CSRE2.RECIVER IS NOT NULL"
        End If
        '子工艺判断
        'strSql += " and ((CASE WHEN TOWS.Steptype=2 THEN tows.subworkflowid ELSE w.workflowid END)=w.workflowid OR (CASE WHEN TOWS.Steptype=2 THEN tows.subworkflowbaseid ELSE w.workflowbaseid END)=w.workflowbaseid)"
        ' strSql += " AND (CASE WHEN TOWS.Steptype=2 THEN tows.Workflowid ELSE '1' END)=(CASE WHEN TOWS.Steptype=2 THEN SWC.WORKFLOWID ELSE '1' END) "
        '
        strSql += " GROUP BY C.CONTAINERNAME, C.CONTAINERID,C.CONTAINERTYPE,C.QTY, FA.FACTORYNAME, CSR.FACTORYID,CSR.CONTAINERSHIPRECORDID,ws.sequence,w.workflowid,  wb.WorkFlowName,w.WorkFlowRevision,CC.CONTAINERNAME,CC.CONTAINERID,csre.acceptanceqty,csre.lossqty,csre.defectqty,csre.reworkqty,CSRE.NONCONFORMINGNO,CSRE.SCRAPNO,WC.WORKCENTERNAME,CSR.Toworkflowstepid,WB2.Workflowname ,W2.WORKFLOWREVISION , TOWS.workflowstepname ,TOWS.Steptype ,CSRE.CONTAINERSHIPRECORDID,C.QTY2,U.UOMNAME,tows.sequence,csre.completedate,csr.Toacceptdate,csr.Needcompletedate,csr.senddate,csr.EstimatedCompleteDate,csr.senddate,W2.workflowid,csre.containerid"
        strSql += " order by csr.senddate desc"
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        If dt.Rows.Count <= 1 Then
            Return dt
        End If
        Dim newDt As DataTable = dt.Clone
        Dim strTem As String = dt.Rows(0)("Toworkflowstepid").ToString
        For Each shipRow As DataRow In dt.Rows
            If shipRow("Toworkflowstepid") = strTem Then
                newDt.ImportRow(shipRow)
            End If
        Next
        newDt.DefaultView.Sort = "containername asc"
        newDt = newDt.DefaultView.ToTable
        Return newDt
    End Function
    ''' <summary>
    ''' 插入转工完成信息V2.0
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function InsertContainerShipRecive(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSContainerShipIBusiness.InsertContainerShipRecive
        Dim strSql As String = " INSERT INTO CONTAINERSHIPRECIVE(COMPLETEDATE,COMPLETEQTY,CONTAINERID,CONTAINERSHIPRECORDID,EMPLOYEEID,FACTORYID,ISUSE,TOFACTORYID,WORKFLOWSTEPID,ISTRANSFER,LOSSQTY,ACCEPTANCEQTY,DEFECTQTY,REWORKQTY,NONCONFORMINGNO,SCRAPNO)" &
                                " VALUES(SYSDATE,:COMPLETEQTY,:CONTAINERID,:CONTAINERSHIPRECORDID,:EMPLOYEEID,:FACTORYID, 0,:TOFACTORYID,:WORKFLOWSTEPID,0,:LOSSQTY,:ACCEPTANCEQTY,:DEFECTQTY,:REWORKQTY,:NONCONFORMINGNO,:SCRAPNO) "
        Dim insertPara(12) As OracleParameter
        insertPara(0) = DatabaseHelper.NewParameter(":COMPLETEQTY", para("CompleteQty"), OracleType.Number)
        insertPara(1) = DatabaseHelper.NewParameter(":CONTAINERID", para("ContainerID"), OracleType.VarChar)
        insertPara(2) = DatabaseHelper.NewParameter(":CONTAINERSHIPRECORDID", para("ContainerShipRecordID"), OracleType.VarChar)
        insertPara(3) = DatabaseHelper.NewParameter(":EMPLOYEEID", para("EmployeeID"), OracleType.VarChar)
        insertPara(4) = DatabaseHelper.NewParameter(":FACTORYID", para("FactoryID"), OracleType.VarChar)
        insertPara(5) = DatabaseHelper.NewParameter(":TOFACTORYID", para("ToFactoryID"), OracleType.VarChar)
        insertPara(6) = DatabaseHelper.NewParameter(":WORKFLOWSTEPID", para("WorkFlowStepID"), OracleType.VarChar)

        insertPara(7) = DatabaseHelper.NewParameter(":LOSSQTY", Convert.ToInt32(para("LossQty")), OracleType.Number)
        insertPara(8) = DatabaseHelper.NewParameter(":ACCEPTANCEQTY", Convert.ToInt32(para("AcceptanceQty")), OracleType.Number)
        insertPara(9) = DatabaseHelper.NewParameter(":DEFECTQTY", Convert.ToInt32(para("DefectQty")), OracleType.VarChar)
        insertPara(10) = DatabaseHelper.NewParameter(":REWORKQTY", Convert.ToInt32(para("ReWorkQty")), OracleType.VarChar)

        insertPara(11) = DatabaseHelper.NewParameter(":NONCONFORMINGNO", para("NonconformingNO"), OracleType.VarChar)
        insertPara(12) = DatabaseHelper.NewParameter(":SCRAPNO", para("ScrapNO"), OracleType.VarChar)
        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql, insertPara)
        If i > 0 Then
            InsertContainerShipRecive = True
            '更新总表一些信息
            m_ConCurrentinfoObj.UpdateContainerShip(para("ContainerID"))
            '
            Exit Function
        End If
        InsertContainerShipRecive = False
    End Function
    ''' <summary>
    ''' 主制厂转入时更新的数据V2.0
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function UpdateContainerShipRecive(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSContainerShipIBusiness.UpdateContainerShipRecive
        Dim strSql As String = "  UPDATE CONTAINERSHIPRECIVE CSRE SET RECIVEDATE = SYSDATE,RECIVER = :RECIVER,ISTRANSFER=0" &
                                " WHERE CSRE.CONTAINERSHIPRECORDID = :CONTAINERSHIPRECORDID"
        If para.ContainsKey("ContainerID") Then
            strSql += String.Format(" AND CSRE.CONTAINERID='{0}'", para("ContainerID"))
        End If
        Dim updatePara(1) As OracleParameter
        'updatePara(0) = DatabaseHelper.NewParameter(":ACCEPTANCEQTY", para("AcceptanceQty"), OracleType.VarChar)
        'updatePara(1) = DatabaseHelper.NewParameter(":LOSSQTY", para("LossQty"), OracleType.Number)
        updatePara(0) = DatabaseHelper.NewParameter(":RECIVER", para("Reciver"), OracleType.VarChar)
        updatePara(1) = DatabaseHelper.NewParameter(":CONTAINERSHIPRECORDID", para("ContainerShipRecordID"), OracleType.VarChar)

        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql, updatePara)
        If i > 0 Then
            UpdateContainerShipRecive = True
            '更新总表一些信息
            m_ConCurrentinfoObj.UpdateContainerShip(para("ContainerID"))
            '
            Exit Function
        End If
        UpdateContainerShipRecive = False
    End Function
    ''' <summary>
    ''' 根据当前containerid和工序ID获取originalFactorydID和工序所属车间ID
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetFactoryID(ByVal ContainerID As String, ByVal WorkFlowStepID As String) As DataTable Implements uLMSContainerShipIBusiness.GetFactoryID
        Dim strSql2 As String = String.Format("SELECT C.ORIGINALFACTORYID FROM Container C WHERE C.CONTAINERID='{0}'", ContainerID)
        Dim dt2 As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql2)

        Dim strSql As String = "SELECT NVL(S.FACTORYID,W2.FACTORYID) FACTORYID,W2.WORKFLOWID FROM WORKFLOWSTEP WS" &
                            "  LEFT JOIN SPECBASE SB ON SB.SPECBASEID=WS.SPECBASEID" &
                            " LEFT JOIN SPEC S ON S.SPECID= NVL(SB.REVOFRCDID,WS.SPECID)" &
                            " LEFT JOIN WORKFLOWBASE WB2 ON WB2.WORKFLOWBASEID=WS.SUBWORKFLOWBASEID" &
       "  LEFT JOIN WORKFLOW W2 ON W2.WORKFLOWID=NVL(WB2.REVOFRCDID,WS.SUBWORKFLOWID)"
        strSql += String.Format(" WHERE WS.WORKFLOWSTEPID = '{0}'", WorkFlowStepID)
        strSql += " GROUP BY NVL(S.FACTORYID,W2.FACTORYID),W2.WORKFLOWID"
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)

        Dim result As New DataTable
        result.Columns.Add("ORIGINALFACTORYID")
        If dt2 IsNot Nothing And dt2.Rows.Count <> 0 Then
            result.Columns("ORIGINALFACTORYID").DefaultValue = dt2.Rows(0)("ORIGINALFACTORYID")
        End If
        result.Columns.Add("FACTORYID")
        result.Columns.Add("SUBWORKFLOWID")
        If dt IsNot Nothing And dt.Rows.Count <> 0 Then
            result.Columns("FACTORYID").DefaultValue = dt.Rows(0)("FACTORYID")
            result.Columns("SUBWORKFLOWID").DefaultValue = dt.Rows(0)("WORKFLOWID")
        End If
        result.Rows.Add()
        GetFactoryID = result
        'GetFactoryIDOfSpec = dt.Rows(0)("FACTORYID")
    End Function
    ''' <summary>
    ''' 当工序承制分厂不是本分厂时插入到转工表
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function InsertShipNotApplied(ByVal para As Dictionary(Of String, String), ByRef ContainerShipID As String) As Boolean Implements uLMSContainerShipIBusiness.InsertShipNotApplied
        'Dim factoryData As DataTable = GetFactoryID(para("ContainerID"), para("WorkFlowStepID"))
        'Dim originalFactoryID As String = factoryData.Rows(0)("ORIGINALFACTORYID")
        'Dim specFactorID As String = factoryData.Rows(0)("FACTORYID")
        'If originalFactoryID <> para("FactoryID") Then
        '    Exit Function
        'End If
        '判断
        'If para("FactoryID") = factoryOfSpec Then
        '    InsertShipNotApplied = False
        '    Exit Function
        'End If

        Dim checkShipSql = String.Format("delete from containershiprecord csr WHERE csr.containerid='{0}' AND csr.workflowstepid='{1}' and isuse=0 and csr.senddate is null", para("ContainerID"), para("WorkFlowStepID"))
        DatabaseHelper.ExecuteSql(checkShipSql)

        Dim strSql As String = " INSERT INTO CONTAINERSHIPRECORD(PROPOSERID,PREPOSERDATE,ISUSE,CONTAINERSHIPRECORDID,FACTORYID," &
            " CONTAINERID,WORKFLOWSTEPID,TOWORKFLOWSTEPID,  AUDITDATE,AUDITOR,AUDITRESULT,TOFACTORYID,ISTRANSFER)" &
            " VALUES (:PROPOSERID,SYSDATE,0,:CONTAINERSHIPRECORDID,:FACTORYID,:CONTAINERID,:WORKFLOWSTEPID,:TOWORKFLOWSTEPID," &
            "   SYSDATE,:AUDITOR,0,:TOFACTORYID,0)"
        Dim insertPara(7) As OracleParameter
        insertPara(0) = DatabaseHelper.NewParameter(":PROPOSERID", "", OracleType.VarChar)
        ContainerShipID = Guid.NewGuid.ToString
        insertPara(1) = DatabaseHelper.NewParameter(":CONTAINERSHIPRECORDID", ContainerShipID, OracleType.VarChar)
        insertPara(2) = DatabaseHelper.NewParameter(":FACTORYID", para("FactoryID"), OracleType.VarChar)
        insertPara(3) = DatabaseHelper.NewParameter(":CONTAINERID", para("ContainerID"), OracleType.VarChar)
        insertPara(4) = DatabaseHelper.NewParameter(":WORKFLOWSTEPID", para("WorkFlowStepID"), OracleType.VarChar)
        insertPara(5) = DatabaseHelper.NewParameter(":TOWORKFLOWSTEPID", para("ToWorkFlowStepID"), OracleType.VarChar)
        insertPara(6) = DatabaseHelper.NewParameter(":AUDITOR", "", OracleType.VarChar)
        insertPara(7) = DatabaseHelper.NewParameter(":TOFACTORYID", para("specFactorID"), OracleType.VarChar)

        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql, insertPara)
        If i > 0 Then
            InsertShipNotApplied = True
            '更新总表
            Dim updatePara As New Dictionary(Of String, String)
            Dim conditionPara As New Dictionary(Of String, String)
            conditionPara("ContainerID") = "'" + para("ContainerID") + "'"
            updatePara("shipid") = "'" + ContainerShipID + "'"
            updatePara("shipnum") = 3
            updatePara("isship") = 1
            m_ConCurrentinfoObj.UpdateContainerCurrentInfoByField(updatePara, conditionPara)
            m_ConCurrentinfoObj.UpdateTransferOfTable("CONTAINERSHIPRECORD", "CONTAINERSHIPRECORDID", ContainerShipID)
            '
            Exit Function
        End If
        InsertShipNotApplied = False
    End Function
    ''' <summary>
    ''' 根据工艺流程ID和工序号获取工序ID
    ''' </summary>
    ''' <param name="sequence"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetNextWorkFlowID(ByVal workFlowID As String, ByVal sequence As String) As String Implements uLMSContainerShipIBusiness.GetNextWorkFlowID
        Dim strSql As String = String.Format("SELECT WS.WORKFLOWSTEPID FROM WORKFLOWSTEP WS WHERE WS.WORKFLOWID='{0}' AND WS.SEQUENCE={1}", workFlowID, sequence)
        Dim dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        If dt.Rows.Count > 0 Then
            GetNextWorkFlowID = dt.Rows(0)("WORKFLOWSTEPID").ToString
        Else
            GetNextWorkFlowID = ""
        End If
    End Function
    ''' <summary>
    ''' 获取工序信息
    ''' </summary>
    ''' <param name="workFlowID"></param>
    ''' <param name="sequence"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkFlowStep(ByVal workFlowID As String, ByVal sequence As String) As DataTable Implements uLMSContainerShipIBusiness.GetWorkFlowStep
        Dim strSql As String = String.Format("SELECT WS.* FROM WORKFLOWSTEP WS WHERE WS.WORKFLOWID='{0}' AND WS.SEQUENCE={1}", workFlowID, sequence)
        Dim dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        GetWorkFlowStep = dt
    End Function

    ''' <summary>
    ''' 固定转工
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function FixedContainerShip(ByVal para As Dictionary(Of String, String), ByVal m_CommonObj As uLMSCommonIBusiness) As Boolean Implements uLMSContainerShipIBusiness.FixedContainerShip
        FixedContainerShip = True
        LogHelp.SetConfig()
        Dim factoryData As DataTable = GetFactoryID(para("ContainerID"), para("WorkFlowStepID"))
        Dim originalFactoryID As String = factoryData.Rows(0)("ORIGINALFACTORYID")
        Dim specFactorID As String = ""
        If Convert.IsDBNull(factoryData.Rows(0)("FACTORYID")) = False Then
            specFactorID = factoryData.Rows(0)("FACTORYID")
        Else
            Exit Function '若该工序没有指定分厂则跳出
        End If
        If originalFactoryID <> para("UserInfoFactory") Then '当创建批次的工厂不是本分厂时跳出(无权进行)  '改user_Info
            Exit Function
        End If
        If specFactorID = para("UserInfoFactory") Then '下一工序的所属工厂是本分厂跳出
            LogHelp.WriteOrcale("自动转工检测" + "@^" + "移到的序不是转工工序" + "@^" + para("ContainerID") + "@^" + Convert.ToString(Guid.NewGuid()) + "@^" + para("ContainerID")) 'add:Wangjh 写入数据库   
            Exit Function
        End If
        para("specFactorID") = specFactorID
        Dim para2 As New Dictionary(Of String, String)
        para2("WorkflowID") = para("WorkflowID")
        para2("Sequence") = para("Sequence")
        Dim SpecInfo As DataTable = m_CommonObj.GetWorkFlowStepsWithoutSelf(para2)
        Dim index As New List(Of Integer) '获取所有工序所属分厂不是本分厂的索引
        For i As Integer = 0 To SpecInfo.Rows.Count - 1
            Dim workFlowStepID = SpecInfo.Rows(i)("WORKFLOWSTEPID").ToString
            para("CheckedWorkflowStepID") = workFlowStepID
            If GetCancleAutoShipOrComission(para).Rows.Count > 0 Then '存在在取消的自动转工序
                If i = 0 Then '第一序就被取消
                    FixedContainerShip = True
                    Exit Function
                Else
                    Continue For
                End If
            End If
            Dim faDt As New DataTable
            faDt = GetFactoryID(Nothing, workFlowStepID)
            Dim specFactoryOfRow As String = ""
            If Convert.IsDBNull(faDt) = False And faDt.Rows.Count <> 0 Then
                specFactoryOfRow = faDt.Rows(0)("FACTORYID").ToString
            End If

            If specFactoryOfRow = specFactorID Then
                index.Add(i)
                para("ToWorkFlowStepID") = workFlowStepID
            Else
                If Not para.ContainsKey("ToWorkFlowStepID") Then
                    para("ToWorkFlowStepID") = para("WorkFlowStepID")
                End If
                Exit For
            End If
        Next
        LogHelp.WriteOrcale("自动转工检测" + "@^" + "有自动转工工序" + "@^" + para("ContainerID") + "@^" + Convert.ToString(Guid.NewGuid()) + "@^" + para("ContainerID")) 'add:Wangjh 写入数据库   
        '删除一次已经创建的记录 必须是未转出的
        Dim checkShipSql As String = String.Format("select * from containershiprecord csr left join containershiprecordstep csrs on csrs.containershiprecordid=csr.containershiprecordid WHERE csr.containerid='{0}' AND csrs.workflowstepid='{1}' and csr.isuse=0", para("ContainerID"), para("WorkFlowStepID"))
        Dim checkShipDt As DataTable = DatabaseHelper.Query_ReturnDatTable(checkShipSql)
        If checkShipDt.Rows.Count > 0 Then
            If Not String.IsNullOrWhiteSpace(checkShipDt.Rows(0)("senddate").ToString) Then '是做过转出操作
                LogHelp.WriteOrcale("自动转工检测" + "@^" + "已有转出记录且已转出" + "@^" + para("ContainerID") + "@^" + Convert.ToString(Guid.NewGuid()) + "@^" + para("ContainerID")) 'add:Wangjh 写入数据库   
                FixedContainerShip = True
                Exit Function
            End If
            'checkShipSql = String.Format("delete from containershiprecord csr WHERE csr.containerid='{0}' AND csr.workflowstepid='{1}' and csr.senddate is null", para("ContainerID"), para("WorkFlowStepID"))
            'DatabaseHelper.ExecuteSql(checkShipSql)
        End If
        '
        Dim ContainerShipID As String = ""
        If Not InsertShipNotApplied(para, ContainerShipID) Then
            FixedContainerShip = False
            Exit Function
        End If
        LogHelp.WriteOrcale("自动转工检测" + "@^" + "自动转工创建成功ID" + ContainerShipID + "@^" + para("ContainerID") + "@^" + Convert.ToString(Guid.NewGuid()) + "@^" + para("ContainerID")) 'add:Wangjh 写入数据库   
        For i As Integer = 0 To index.Count - 1
            Dim workflowStepID As String = SpecInfo.Rows(index(i))("WORKFLOWSTEPID")
            If Not InsertContainerShipStep(ContainerShipID, workflowStepID) Then
                FixedContainerShip = False
                Exit Function
            End If
        Next
    End Function

    ''' <summary>
    ''' 获取所有已临时转工的工序信息 create tianFW
    ''' </summary>
    ''' <param name="conotainerid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetShipWorkflowStepIDs(ByVal conotainerid As String) As DataTable Implements IBusiness.uLMSContainerShipIBusiness.GetShipWorkflowStepIDs
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CSCS.WORKFLOWSTEPID FROM CONTAINERSHIPRECORD CC LEFT JOIN CONTAINERSHIPRECORDSTEP CSCS ON CSCS.CONTAINERSHIPRECORDID = CC.CONTAINERSHIPRECORDID")
        strsql.AppendLine(" LEFT JOIN containershiprecive csr ON csr.containershiprecordid=cc.containershiprecordid")
        strsql.AppendFormat(" WHERE ((cc.proposerid IS NOT NULL AND cc.auditor IS NULL ) OR (cc.proposerid IS NOT NULL AND cc.auditresult=0 AND csr.reciver IS NULL)) AND ({0})", conotainerid)

        strsql.AppendLine(" and cc.isuse=0 ")
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
    End Function
    ''' <summary>
    ''' 指定批次的指定工序是否已存在转工申请
    ''' create:201-8-7s
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function IsExitsContainerShip(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSContainerShipIBusiness.IsExitsContainerShip
        Dim strSql As String = " SELECT COUNT(*) NUM FROM containershiprecord csr " &
                                " INNER JOIN containershiprecordstep csrs ON csrs.containershiprecordid=csr.containershiprecordid"
        strSql += String.Format(" WHERE csr.containerid='{0}' AND csrs.workflowstepid='{1}' AND csr.isuse=0", para("ContainerID"), para("Workflowstepid"))

        Dim i As Integer = DatabaseHelper.Query_ReturnDatTable(strSql).Rows(0)("num")
        If i > 0 Then
            IsExitsContainerShip = True
        Else
            IsExitsContainerShip = False
        End If
    End Function
    ''' <summary>
    ''' 插入直接转回本分厂的数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function InsertShipBackOfNoComplete(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSContainerShipIBusiness.InsertShipBackOfNoComplete
        Dim insertSql As String = " INSERT INTO containershiprecive csr (recivedate,reciver,containerID,ContainerShipRecordID,isuse,istransfer)"
        insertSql += String.Format("VALUES(sysdate,'{0}','{1}','{2}',0,0)",
                                   para("Reciver"), para("ContainerID"), para("ContainerShipRecordID"))

        Dim i As Integer = DatabaseHelper.ExecuteSql(insertSql)
        If i > 0 Then
            InsertShipBackOfNoComplete = True
            Exit Function
        End If
        InsertShipBackOfNoComplete = False
    End Function
    ''' <summary>
    ''' 更新结转标记
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateShipTransfer(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSContainerShipIBusiness.UpdateShipTransfer
        Dim updateSql As String = "UPDATE containershiprecord csr SET csr.istransfer=0 WHERE csr.isuse=0"
        updateSql += String.Format("and csr.containerid='{0}' AND csr.workflowstepid='{1}'", para("ContainerID"), para("WorkFlowStepID"))
        Dim i As Integer = DatabaseHelper.ExecuteSql(updateSql)
        If i > 0 Then
            UpdateShipTransfer = True
        Else
            UpdateShipTransfer = False
        End If
    End Function

    ''' <summary>
    ''' 通过批次ID获取批次的转工记录
    ''' create tianFW 2017年12月27日
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks> 
    Protected Function GetHistoryShipInfo(ByVal containerID As String, ByVal type As Integer) As DataTable Implements uLMSContainerShipIBusiness.GetHistoryShipInfo
        Dim strSql As New StringBuilder
        strSql.AppendLine("SELECT CSR.*, EM1.FULLNAME PREPOSERNAME, EM2.FULLNAME AUDITNAME,'' CONTAINERNAME,csr.PreposerDate TXNDATE")
        strSql.AppendLine(",WS1.WORKFLOWSTEPNAME||' : '||WS2.WORKFLOWSTEPNAME WORKFLOWSTEPNAME,csre.completedate,csre.ReciveDate ")
        strSql.AppendLine("FROM CONTAINERSHIPRECORD CSR")
        strSql.AppendLine("LEFT JOIN containershiprecive csre ON csre.containershiprecordid=csr.containershiprecordid and csre.containerid=csr.containerid and csre.isuse=0") 'add:Wangjh 
        strSql.AppendLine("LEFT JOIN EMPLOYEE EM1 ON EM1.EMPLOYEEID = CSR.PROPOSERID")
        strSql.AppendLine("LEFT JOIN EMPLOYEE EM2 ON EM2.EMPLOYEEID = CSR.AUDITOR")
        strSql.AppendLine("LEFT JOIN WORKFLOWSTEP WS1 ON WS1.WORKFLOWSTEPID = CSR.WORKFLOWSTEPID")
        strSql.AppendLine("LEFT JOIN WORKFLOWSTEP WS2 ON WS2.WORKFLOWSTEPID = CSR.TOWORKFLOWSTEPID")
        strSql.AppendLine("left join containercurrentinfo cci on cci.containerid=csr.containerid")
        strSql.AppendLine("WHERE CSR.ISUSE = 0 and (ws1.sequence<=cci.workflowstepsequence and ws2.sequence>=cci.workflowstepsequence) ")
        If type = 1 Then
            strSql.AppendFormat(" AND CSR.CONTAINERID='{0}'", containerID)
        Else
            strSql.AppendFormat(" AND CSR.CONTAINERID IN ({0})", containerID)
        End If
        Return DatabaseHelper.Query_ReturnDatTable(strSql.ToString())
    End Function

    ''' <summary>
    ''' 获取批次当前序是否存在已接收的转工记录 create by tianFW 2018年10月11日
    ''' </summary>
    ''' <param name="containerIDs">批次ID</param>
    ''' <param name="workflowstepID">批次当前序ID</param>
    ''' <returns></returns>
    ''' <remarks>去除AND CSR.TOACCEPTOR IS NOT NULL的查询条件 只要当前序存在转工记录，就不能撤销</remarks>
    Protected Function GetContainerCurrShipInfo(ByVal containerIDs As String, ByVal workflowstepID As String) As DataTable Implements uLMSContainerShipIBusiness.GetContainerCurrShipInfo
        Dim strSql As New StringBuilder
        strSql.AppendLine("SELECT CSR.*, EM1.FULLNAME PREPOSERNAME, EM2.FULLNAME AUDITNAME,'' CONTAINERNAME,csr.PreposerDate TXNDATE")
        strSql.AppendLine(",WS1.WORKFLOWSTEPNAME||' : '||WS2.WORKFLOWSTEPNAME WORKFLOWSTEPNAME")
        strSql.AppendLine("FROM CONTAINERSHIPRECORD CSR")
        strSql.AppendLine("LEFT JOIN EMPLOYEE EM1 ON EM1.EMPLOYEEID = CSR.PROPOSERID")
        strSql.AppendLine("LEFT JOIN EMPLOYEE EM2 ON EM2.EMPLOYEEID = CSR.AUDITOR")
        strSql.AppendLine("LEFT JOIN WORKFLOWSTEP WS1 ON WS1.WORKFLOWSTEPID = CSR.WORKFLOWSTEPID")
        strSql.AppendLine("LEFT JOIN WORKFLOWSTEP WS2 ON WS2.WORKFLOWSTEPID = CSR.TOWORKFLOWSTEPID")
        strSql.AppendLine("LEFT JOIN (SELECT ws.workflowid,ws.sequence,ws.workflowstepname ")
        strSql.AppendLine("FROM Workflowstep ws")
        strSql.AppendFormat(" WHERE ws.workflowstepid='{0}'  ", workflowstepID)
        strSql.AppendLine(") K ON ws1.workflowid = k.workflowid AND ws2.workflowid = k.workflowid")
        strSql.AppendLine("WHERE CSR.ISUSE = 0 AND ws1.sequence<=k.sequence AND ws2.sequence>=k.sequence")
        strSql.AppendFormat(" AND CSR.CONTAINERID IN ({0})", containerIDs)
        Return DatabaseHelper.Query_ReturnDatTable(strSql.ToString())
    End Function

    ''' <summary>
    ''' 根据转工唯一ID获取转工信息 create tianFW 2018年3月16日
    ''' </summary>
    ''' <param name="ID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetShipInfoByID(ByVal ID As String) As DataTable Implements uLMSContainerShipIBusiness.GetShipInfoByID
        Dim strsql As String = String.Format("SELECT CSR.* FROM CONTAINERSHIPRECORD CSR WHERE CSR.CONTAINERSHIPRECORDID='{0}'", ID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 根据转工唯一ID获取转工及工序信息 create tianFW 2018年3月16日
    ''' </summary>
    ''' <param name="ID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetShipStepInfoByID(ByVal ID As String) As DataTable Implements uLMSContainerShipIBusiness.GetShipStepInfoByID
        Dim strsql As String = String.Format("SELECT CSR.* FROM CONTAINERSHIPRECORDSTEP CSR WHERE CSR.CONTAINERSHIPRECORDID='{0}'", ID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function
    ''' <summary>
    ''' 获取处于待转出到未转回的所有转工记录
    ''' add:Wangjh 20180418
    ''' modify by tianFW 2018年10月18日 将单件单卡合并一条查询
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetNoCompleteShipInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSContainerShipIBusiness.GetNoCompleteShipInfo
        Dim strSql As String = "SELECT cci.productname,cci.containerno,sum(cci.qty) qty,fa.factoryname," &
                               " DECODE(cci.shipnum,3,'待转出',4,'待接收',5,'已接收','6','待完成',7,'待转入') shipstatus ,cci.fromworkcentername workcentername" &
                                " FROM containercurrentinfo cci" &
                                " LEFT JOIN containershiprecord csr ON csr.containershiprecordid=cci.shipid" &
                                " LEFT JOIN factory fa ON fa.factoryid=csr.tofactoryid" &
                               " WHERE cci.isship=1 AND cci.shipnum>=3 "
        If para.ContainsKey("OriginalFactoryID") Then
            strSql += String.Format(" AND cci.ORIGINALFACTORYID='{0}'", para("OriginalFactoryID"))
        End If
        'add YangJiang 根据工区或班组过滤 20180504
        If para.ContainsKey("WorkCenterName") Then
            strSql += String.Format(" AND cci.fromworkcentername='{0}'", para("WorkCenterName"))
        End If
        If para.ContainsKey("TeamID") Then
            strSql += String.Format(" AND cci.TEAMID='{0}'", para("TeamID"))
        End If
        strSql += " group by cci.productname,cci.containerno,cci.qty,fa.factoryname,cci.shipnum,cci.fromworkcentername"
        GetNoCompleteShipInfo = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取批次对应子工艺的父工序信息
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetFaWorkFlowStepOfNoShip(ByVal containerID As String, ByVal subWorkFlowID As String) As DataTable Implements uLMSContainerShipIBusiness.GetFaWorkFlowStepOfNoShip
        Dim strSql As String = <string>
                            SELECT ws.workflowstepid,ws.workflowstepname,ws.sequence FROM subworkflowcheckinfo swc  
                            LEFT JOIN Workflowstep ws ON ws.workflowstepid=swc.workflowstepid
                               </string>
        strSql += String.Format(" WHERE swc.containerid='{0}' AND swc.subworkflowid='{1}'", containerID, subWorkFlowID)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        Return dt
    End Function

    ''' <summary>
    ''' 插入转工退回信息
    ''' add:Wangjh 
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function InserTaskReturnInfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSContainerShipIBusiness.InserTaskReturnInfo
        Dim strSql As String = String.Format("SELECT *  FROM processdealrecord p WHERE  p.parentid='{0}' AND p.processtype IN (1,4) AND p.dealdate IS NULL", para("ParentID"))
        If DatabaseHelper.Query_ReturnDatTable(strSql).Rows.Count > 0 Then
            Return True '已有记录
        End If
        strSql = <string>
                                    INSERT INTO  ProcessDealRecord pdl (pdl.createdate,pdl.employeeid,pdl.parentid,pdl.processstatus,pdl.processtype,pdl.reasonnote) VALUES(
                                    SYSDATE,:employeeid,:parentid,'0',:processtype,:reasonnote
                                    )
                               </string>
        Dim insertPara(3) As OracleParameter
        insertPara(0) = DatabaseHelper.NewParameter(":employeeid", para("EmployeeID"), OracleType.VarChar)
        insertPara(1) = DatabaseHelper.NewParameter(":parentid", para("ParentID"), OracleType.VarChar)
        insertPara(2) = DatabaseHelper.NewParameter(":ReasonNote", para("ReasonNote"), OracleType.VarChar)
        insertPara(3) = DatabaseHelper.NewParameter(":processtype", para("Processtype"), OracleType.VarChar)
        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql, insertPara)
        If (i > 0) Then
            If para("Processtype") = "4" Then
                strSql = String.Format("UPDATE containershiprecord csr SET csr.toacceptor=null,csr.toacceptdate=NULL,csr.toworkcenterid=NULL,csr.Estimatedcompletedate=NULL WHERE csr.containershiprecordid='{0}'", para("ParentID"))
                If DatabaseHelper.ExecuteSql(strSql) > 0 Then
                    strSql = String.Format("UPDATE containercurrentinfo cci SET cci.shipnum=4 WHERE cci.shipid='{0}'", para("ParentID"))
                    DatabaseHelper.ExecuteSql(strSql)
                End If
            End If
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 转工退回后确认
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ConfirmShipReturn(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSContainerShipIBusiness.ConfirmShipReturn
        ConfirmShipReturn = False
        Dim strSql As String = <string>
                            UPDATE ProcessDealRecord pdl SET pdl.dealdate=SYSDATE,pdl.dealemployeeid=:dealemployeeid ,pdl.ProcessStatus='1'
                            WHERE pdl.parentid=:parentid
                               </string>
        Dim sqlPara(1) As OracleParameter
        sqlPara(0) = DatabaseHelper.NewParameter(":dealemployeeid", para("EmployeeID"), OracleType.VarChar)
        sqlPara(1) = DatabaseHelper.NewParameter(":parentid", para("ParentID"), OracleType.VarChar)

        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql, sqlPara)
        If (i > 0) Then
            Return True
        Else
            Return False
        End If
        ConfirmShipReturn = True
    End Function

    ''' <summary>
    ''' 获取含有自动转工或外委的批次
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetAutoShipOrCommissioning(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSContainerShipIBusiness.GetAutoShipOrCommissioning
        Dim strSql As String = <string>
                                  SELECT c.mfgordername,c.togetherflag,(nvl(c.workflowroute,c.productname)||'/'||c.containerno) ProductBatch,(C.productname||'/'||c.containerno) ProductBatchHidden,c.PRODUCTDESCRIPTION,c.PRODUCTID,c.PRODUCTNAME,c.PRODUCTREVISION,
                                 c.PLANQTY SUMPLANQTY,count(distinct c.containerid) sumQty,c.workflowstepname,c.workflowstepid,c.workflowid,c.containertype,DECODE(c.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,
                                wm_concat(DISTINCT ws.workflowstepid) workflowstepids,'select * from containercurrentinfo c where 1=1 ' ChildContainerSql
                                 FROM containercurrentinfo c
                                LEFT JOIN workflowstep ws ON ws.workflowid=c.workflowid
                                LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
                                LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
                                WHERE c.containerstatus=1 and c.isinstore!=1 and (s.factoryid!=c.originalfactoryid OR ws.controlcode='LY05' OR S.CONTROLCODE='LY05')
                              </string>
        strSql += String.Format(" and c.originalfactoryid='{0}'", para("FactoryID"))
        strSql += GetCondition(para)
        strSql += "   GROUP BY c.PLANQTY,c.mfgordername,c.workflowroute,c.productname,c.containerno,c.productdescription,c.productid,c.PRODUCTNAME,c.PRODUCTREVISION,c.togetherflag,c.workflowstepname,c.workflowstepid,c.workflowid,c.containertype"
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql

        Return DatabaseHelper.RunProcedureForPage(paraPage)

    End Function

    ''' <summary>
    ''' 获取工序转工或外委信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepShipOrCommission(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSContainerShipIBusiness.GetStepShipOrCommission
        Dim strSql As String = <string>
                                SELECT s.factoryid,decode(NVL(ws.controlcode,s.controlcode),'LY05','是','否') iscommissioning,cci.containername,
                                cci.containerid,ws.workflowstepname,ws.workflowstepid,csn.containerstepnoshipid,csn.Noship,csn.EmployeeID,e.fullname,
                                csn.containerstepnoshipid,
                                csn.CreateDate,csn.isuse  FROM containercurrentinfo cci
                                LEFT JOIN Workflowstep ws ON ws.workflowid=cci.workflowid
                                LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
                                LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
                                LEFT JOIN containerstepNoShip csn ON csn.containerid=cci.containerid AND csn.workflowstepid=ws.workflowstepid
                                LEFT JOIN employee e ON e.employeeid=csn.employeeid
                               </string>
        If para("type") = "2" Then '子卡工序不加工
            strSql += String.Format(" WHERE cci.containerid IN ({0}) and ws.Sequence>={1}", para("ContainerIDs"), para("Sequence"))
        Else
            strSql += String.Format(" WHERE cci.containerid IN ({0}) and ws.workflowstepid IN ({1})", para("ContainerIDs"), para("WorkflowStepIDs"))
        End If
        strSql += " ORDER BY cci.containername,ws.workflowstepid ASC"
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 插入临时取消转工或外委的批次工序信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function InsertCancleAutoShipOrComission(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSContainerShipIBusiness.InsertCancleAutoShipOrComission
        '取消自动外委
        Dim updateSql As String = <string>UPDATE containercommissioning cmm SET cmm.isuse=1,cmm.istransfer=0 WHERE cmm.isuse=0</string>
        updateSql += String.Format(" AND cmm.containerid='{0}' AND cmm.workflowstepid='{1}'", para("ContainerID"), para("WorkflowStepID"))
        If DatabaseHelper.ExecuteSql(updateSql) > 0 Then
            updateSql = <string>UPDATE containercurrentinfo cmm SET cmm.iscommissioning=0,cmm.commissioningnum=0 </string>
            updateSql = String.Concat(updateSql, String.Format(" where cmm.containerid='{0}' AND cmm.workflowstepid='{1}'", para("ContainerID"), para("WorkflowStepID")))
            DatabaseHelper.ExecuteSql(updateSql)

            LogHelp.WriteOrcale("外委撤销-临时不外委" + "@^" + "批次：" + para("ContainerID") + "外委撤销成功" + "@^" + "开始工序" + para("WorkflowStepID") + "@^" + para("FullName")) 'add:Wangjh 写入数据库   
        End If
        '
        Dim insertSql As String = <string>
                                     INSERT INTO containerstepNoShip csn(csn.containerstepnoshipid,csn.containerid,csn.workflowstepid,csn.noship,csn.employeeid,csn.createdate,csn.isuse)
									 VALUES(:containerstepnoshipid,:containerid,:workflowstepid,:noship,:employeeid,SYSDATE,0)
                                 </string>
        Dim insertPara(4) As OracleParameter
        insertPara(0) = DatabaseHelper.NewParameter(":containerstepnoshipid", Guid.NewGuid.ToString, OracleType.VarChar)
        insertPara(1) = DatabaseHelper.NewParameter(":CONTAINERID", para("ContainerID"), OracleType.VarChar)
        insertPara(2) = DatabaseHelper.NewParameter(":workflowstepid", para("WorkflowStepID"), OracleType.VarChar)
        insertPara(3) = DatabaseHelper.NewParameter(":employeeid", para("EmployeeID"), OracleType.VarChar)
        insertPara(4) = DatabaseHelper.NewParameter(":noship", para("NoShip"), OracleType.VarChar)
        Dim i As Integer = DatabaseHelper.ExecuteSql(insertSql, insertPara)
        If i > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
    ''' <summary>
    ''' 删除批次工序取消外委转工
    ''' </summary>
    ''' <param name="ID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DelContainerStepNoShip(ByVal ID As String) As Boolean Implements uLMSContainerShipIBusiness.DelContainerStepNoShip
        Dim strSql As String = "DELETE containerStepNoShip csn WHERE csn.containerstepnoshipid='" + ID + "'"
        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql)
        If i > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
    ''' <summary>
    ''' 获取批次工序取消的自动转工和外委记录
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetCancleAutoShipOrComission(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSContainerShipIBusiness.GetCancleAutoShipOrComission
        Dim strSql As String = " SELECT * FROM containerStepNoShip csn WHERE csn.isuse=0 AND csn.noship=1 "
        If para.ContainsKey("ContainerID") Then
            strSql += String.Format(" and csn.containerid='{0}' AND csn.workflowstepid='{1}'", para("ContainerID"), para("CheckedWorkflowStepID"))
        ElseIf para.ContainsKey("ContainerIDs") Then
            strSql += String.Format(" and csn.containerid in ({0}) AND csn.workflowstepid='{1}'", para("ContainerIDs"), para("CheckedWorkflowStepID"))
        ElseIf para.ContainsKey("ContainerIDsOr") Then
            strSql += String.Format(" and csn.workflowstepid='{0}' and ({1})", para("CheckedWorkflowStepID"), para("ContainerIDsOr"))
        End If

        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 判断批次对应的转工记录是否有派报工记录0没有 1有派工 2有报工
    ''' add:Wangjh 20181113
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsDispatchOrReportOfConShip(ByVal para As Dictionary(Of String, String)) As Integer Implements uLMSContainerShipIBusiness.IsDispatchOrReportOfConShip
        Dim strSql As String = String.Format(<string>SELECT csd.* FROM containerstepdispatch csd WHERE csd.isuse=0 AND csd.containerid='{0}' AND csd.workflowstepid IN (
        SELECT csrs.workflowstepid  FROM containershiprecordstep csrs WHERE csrs.containershiprecordid='{1}' 
        )</string>.Value, para("ContainerID"), para("ParentID"))
        If DatabaseHelper.Query_ReturnDatTable(strSql).Rows.Count > 0 Then
            Return 1
        End If
        strSql = String.Format(<string>SELECT csd.* FROM containerstepworkreport csd WHERE csd.isuse=0 AND csd.containerid='{0}' AND csd.workflowstepid IN (
        SELECT csrs.workflowstepid  FROM containershiprecordstep csrs WHERE csrs.containershiprecordid='{1}' 
        )</string>.Value, para("ContainerID"), para("ParentID"))
        If DatabaseHelper.Query_ReturnDatTable(strSql).Rows.Count > 0 Then
            Return 2
        Else
            Return 0
        End If
    End Function

#Region "子卡工序不加工"
    ''' <summary>
    ''' 查询子工艺的批次
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSonContainer(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSContainerShipIBusiness.GetSonContainer
        Dim strSql As String = <string>
                                   SELECT c.mfgordername,c.togetherflag,(nvl(c.workflowroute,c.productname)||'/'||c.containerno) ProductBatch,(C.productname||'/'||c.containerno) ProductBatchHidden,c.PRODUCTDESCRIPTION,c.PRODUCTID,c.PRODUCTNAME,c.PRODUCTREVISION,
                                 c.PLANQTY SUMPLANQTY,count(distinct c.containerid) sumQty,c.workflowstepname,c.workflowstepid,c.workflowstepsequence sequence,c.workflowid,c.containertype,DECODE(c.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,'select * from containercurrentinfo c where 1=1 ' ChildContainerSql
                                 FROM containercurrentinfo c 
                                 WHERE c.containerstatus=1 and c.isinstore!=1  AND c.productname LIKE '%子工艺%'
                              </string>
        strSql += String.Format(" and c.originalfactoryid='{0}'", para("FactoryID"))
        strSql += GetCondition(para)
        strSql += " GROUP BY c.PLANQTY,c.mfgordername,c.workflowroute,c.productname,c.containerno,c.productdescription,c.productid,c.PRODUCTNAME,c.PRODUCTREVISION,c.togetherflag,c.workflowstepname,c.workflowstepid,c.workflowstepsequence,c.workflowid,c.containertype"
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql

        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function



#End Region

#Region "批次转工追溯卡（热表）"

    ''' <summary>
    ''' 保存批次转工完成信息v3.0（只限热表） add by zhangrj 20240703
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="msg"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function SaveRbContainerShipRecive(ByVal para As Dictionary(Of String, String), ByRef msg As String) As Boolean Implements uLMSContainerShipIBusiness.SaveRbContainerShipRecive
        Dim filePath As String = String.Empty 'pdf路径
        Try
            Dim strSql As New StringBuilder
            Dim sqlList As New ArrayList
            '插入转工完成
            strSql.AppendLine(" INSERT INTO CONTAINERSHIPRECIVE(COMPLETEDATE,COMPLETEQTY,CONTAINERID,CONTAINERSHIPRECORDID,EMPLOYEEID,FACTORYID,ISUSE,TOFACTORYID,WORKFLOWSTEPID,ISTRANSFER,LOSSQTY,ACCEPTANCEQTY,DEFECTQTY,REWORKQTY,NONCONFORMINGNO,SCRAPNO)" &
                                    " VALUES(SYSDATE, ")
            strSql.AppendFormat("'{0}',", para("CompleteQty"))
            strSql.AppendFormat("'{0}',", para("ContainerID"))
            strSql.AppendFormat("'{0}',", para("ContainerShipRecordID"))
            strSql.AppendFormat("'{0}',", para("EmployeeID"))
            strSql.AppendFormat("'{0}',", para("FactoryID"))
            strSql.AppendLine("0,")
            strSql.AppendFormat("'{0}',", para("ToFactoryID"))
            strSql.AppendFormat("'{0}',", para("WorkFlowStepID"))
            strSql.AppendLine("0,")
            strSql.AppendFormat("'{0}',", para("LossQty"))
            strSql.AppendFormat("'{0}',", para("AcceptanceQty"))
            strSql.AppendFormat("'{0}',", para("DefectQty"))
            strSql.AppendFormat("'{0}',", para("ReWorkQty"))
            strSql.AppendFormat("'{0}',", para("NonconformingNO"))
            strSql.AppendFormat("'{0}')", para("ScrapNO"))
            sqlList.Add(strSql.ToString())
            'Dim insertPara(12) As OracleParameter
            'insertPara(0) = DatabaseHelper.NewParameter(":COMPLETEQTY", para("CompleteQty"), OracleType.Number)
            'insertPara(1) = DatabaseHelper.NewParameter(":CONTAINERID", para("ContainerID"), OracleType.VarChar)
            'insertPara(2) = DatabaseHelper.NewParameter(":CONTAINERSHIPRECORDID", para("ContainerShipRecordID"), OracleType.VarChar)
            'insertPara(3) = DatabaseHelper.NewParameter(":EMPLOYEEID", para("EmployeeID"), OracleType.VarChar)
            'insertPara(4) = DatabaseHelper.NewParameter(":FACTORYID", para("FactoryID"), OracleType.VarChar)
            'insertPara(5) = DatabaseHelper.NewParameter(":TOFACTORYID", para("ToFactoryID"), OracleType.VarChar)
            'insertPara(6) = DatabaseHelper.NewParameter(":WORKFLOWSTEPID", para("WorkFlowStepID"), OracleType.VarChar)

            'insertPara(7) = DatabaseHelper.NewParameter(":LOSSQTY", Convert.ToInt32(para("LossQty")), OracleType.Number)
            'insertPara(8) = DatabaseHelper.NewParameter(":ACCEPTANCEQTY", Convert.ToInt32(para("AcceptanceQty")), OracleType.Number)
            'insertPara(9) = DatabaseHelper.NewParameter(":DEFECTQTY", Convert.ToInt32(para("DefectQty")), OracleType.VarChar)
            'insertPara(10) = DatabaseHelper.NewParameter(":REWORKQTY", Convert.ToInt32(para("ReWorkQty")), OracleType.VarChar)

            'insertPara(11) = DatabaseHelper.NewParameter(":NONCONFORMINGNO", para("NonconformingNO"), OracleType.VarChar)
            'insertPara(12) = DatabaseHelper.NewParameter(":SCRAPNO", para("ScrapNO"), OracleType.VarChar)
            'sqlList.Add(New Object() {strSql, insertPara})

            '插入转工追溯信息
            strSql.Clear()
            strSql.AppendFormat(<string>
                        SELECT csr.senddate,csr.sender,csr.toacceptdate,csr.toacceptor,cc.productname,cc.mfgordername,cc.qty,
                        c.containerid subcontainerid,c.containername subcontainername,ws.workflowstepid,ws.workflowstepname,
                        DECODE(c.isinstore,1,c.bsworkflowid,c.workflowid) subworkflowid
                        FROM containershiprecord csr
                        LEFT JOIN containercurrentinfo cc ON cc.containerid = csr.containerid 
                        LEFT JOIN containershiprecordstep csrr ON csrr.containershiprecordid = csr.containershiprecordid
                        left JOIN workflowstep ws ON ws.workflowstepid = csrr.workflowstepid
                        LEFT JOIN containersubcontainer csc ON csc.containerid = csr.containerid AND csc.workflowstepname = ws.workflowstepname
                        LEFT JOIN containercurrentinfo c ON c.containerid = csc.subcontainerid 
                        WHERE csr.containershiprecordid = '{0}' AND c.containerstatus = 1
                     </string>.Value, para("ContainerShipRecordID"))
            Dim dtShip = DatabaseHelper.Query_ReturnDatTable(strSql.ToString())
            '只有转工序有热表的子卡才插入追溯（可能转工序是外委序、不加工情况）
            If dtShip.Rows.Count > 0 Then
                '获取子卡的流卡信息PDF文件
                filePath = GetRbContainerPrintInfoForPDF(dtShip.Rows(0)("subcontainerid").ToString(), dtShip.Rows(0)("subworkflowid").ToString())

                If String.IsNullOrWhiteSpace(filePath) Then
                    msg = "无法生成PDF，原因：子卡打印数据有误"
                    Return False
                    Exit Function
                End If
                strSql.Clear()
                strSql.AppendLine(<string>
                                 INSERT INTO RbContainerTraceBackInfo(Rbcontainertracebackinfoid,Mfgordername,Productname,
                                                                      Maincontainerid,Maincontainername,Subcontainerid,Subcontainername,
                                                                      Qty,Workflowstepid,Workflowstepname,Containershiprecordid,Sender,Sendetime,
                                                                      Reciver,Recivetime,Completer,Completetime,Createtime,Createby,Filedocpath,Status,Remark)
                                                                VALUES(
                             </string>.Value)
                strSql.AppendFormat("'{0}',", Guid.NewGuid.ToString()) '主ID
                strSql.AppendFormat("'{0}',", dtShip.Rows(0)("Mfgordername").ToString()) '订单号
                strSql.AppendFormat("'{0}',", dtShip.Rows(0)("Productname").ToString()) '件号
                strSql.AppendFormat("'{0}',", para("ContainerID")) '主批次ID
                strSql.AppendFormat("'{0}',", para("ContainerName")) '主批次名
                strSql.AppendFormat("'{0}',", dtShip.Rows(0)("subcontainerid").ToString()) '子批次ID
                strSql.AppendFormat("'{0}',", dtShip.Rows(0)("subcontainername").ToString()) '子批次名
                strSql.AppendFormat("'{0}',", dtShip.Rows(0)("qty").ToString()) '转工数
                strSql.AppendFormat("'{0}',", dtShip.Rows(0)("workflowstepid").ToString()) '转工工序ID
                strSql.AppendFormat("'{0}',", dtShip.Rows(0)("workflowstepname").ToString()) '转工工序名
                strSql.AppendFormat("'{0}',", para("ContainerShipRecordID")) '转工记录ID
                strSql.AppendFormat("'{0}',", dtShip.Rows(0)("sender").ToString()) '转出人
                If String.IsNullOrWhiteSpace(dtShip.Rows(0)("senddate").ToString()) Then '如果转出时间为空，则赋值空值
                    strSql.AppendFormat("NULL,")
                Else
                    strSql.AppendFormat("to_date('{0}','yyyy-mm-dd hh24:mi:ss'),", DateTime.Parse(dtShip.Rows(0)("senddate").ToString()).ToString("yyyy-MM-dd hh:mm:ss")) '转出时间
                End If
                strSql.AppendFormat("'{0}',", dtShip.Rows(0)("toacceptor").ToString()) '接收人
                If String.IsNullOrWhiteSpace(dtShip.Rows(0)("toacceptdate").ToString()) Then '如果接收时间为空，则赋值空值
                    strSql.AppendFormat("NULL,")
                Else
                    strSql.AppendFormat("to_date('{0}','yyyy-mm-dd hh24:mi:ss'),", DateTime.Parse(dtShip.Rows(0)("toacceptdate").ToString()).ToString("yyyy-MM-dd hh:mm:ss")) '接收时间
                End If
                strSql.AppendFormat("'{0}',", para("EmployeeID")) '完成人
                strSql.AppendLine("sysdate,")
                strSql.AppendLine("sysdate,")
                strSql.AppendFormat("'{0}',", para("EmployeeID")) '创建人
                strSql.AppendFormat("'{0}',", filePath) '子卡流水卡打印PDF路径
                strSql.AppendLine("0,")
                strSql.AppendFormat("'{0}')", "") '备注
                sqlList.Add(strSql.ToString())
            End If
            DatabaseHelper.ExecuteSqlTran(sqlList)
            '更新批次总表转工信息
            m_ConCurrentinfoObj.UpdateContainerShip(para("ContainerID"))
            Return True
        Catch ex As Exception
            If Not String.IsNullOrWhiteSpace(filePath) Then
                filePath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + filePath
                If System.IO.File.Exists(filePath) Then
                    System.IO.File.Delete(filePath) '删除掉pdf
                End If
            End If
            msg = "执行出错，原因：" + ex.Message
            Return False
        End Try
    End Function

    ''' <summary>
    ''' 获取热表流卡打印信息（pdf文件）
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <param name="workflowID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetRbContainerPrintInfoForPDF(ByVal containerID As String, ByVal workflowID As String) As String
        Dim filePath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "/Temp/RBContainerPDF/"
        If Not System.IO.Directory.Exists(filePath) Then
            System.IO.Directory.CreateDirectory(filePath)
        End If
        Dim fileName = String.Format("{0}{1}.pdf", containerID, DateTime.Now.ToString("yyyyMMddhhmmssffff"))
        Dim printUrl = String.Format("http://localhost/InSiteWebApplication/forms/Custom/ReBiao/RBProductWorkingPrintPage.html?containerId={0}&workflowId={1}", containerID, workflowID)
        Dim pdf = New NReco.PdfGenerator.HtmlToPdfConverter()
        pdf.GeneratePdfFromFile(printUrl, "", filePath + fileName) '保存PDF
        If System.IO.File.Exists(filePath + fileName) Then '如果保存成功
            '读取保存的pdf内容
            'Dim filestream As New System.IO.FileStream(filePath + fileName, System.IO.FileMode.Open, System.IO.FileAccess.Read)
            'Dim sm As New System.IO.StreamReader(filestream)
            If New System.IO.FileInfo(filePath + fileName).Length > 1500 Then
                Return "/Temp/RBContainerPDF/" + fileName '如果文件大小大于15kb，则表示有数据（只是大概率，无法保障数据极少的情况）
            Else
                System.IO.File.Delete(filePath + fileName) '内容不对，则删除掉
            End If
            'filestream.Close()
            'filestream.Dispose()
        End If
        Return String.Empty
    End Function

#End Region

#Region "推送转工数据到WMS"
    ''' <summary>
    ''' 将转工转出信息汇总后保存到WMS推送表 add by zhangrj 20250918
    ''' </summary>
    ''' <param name="containerShipRecordID"></param>
    ''' <param name="m_UserInfo"></param>
    ''' <param name="message"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SendContainerShipDataToWMS(ByVal containerShipRecordID As String, ByVal m_UserInfo As CurrentUserInfo, ByRef message As String) As Boolean Implements uLMSContainerShipIBusiness.SendContainerShipDataToWMS
        Try
            Dim sql = String.Format(<string>
                        SELECT c.mfgordername,c.containername,c.togetherflag,c.productname,s.specno,u.uomname,
                        c.qty,ap.starttime,c.containertype,csr.containershiprecordid,
                        e.fullname sender,u.attributevalue sendfactorycode,uu.attributevalue receivefactorycode,csr.deliverylocation
                        FROM containershiprecord csr
                        LEFT JOIN containercurrentinfo c ON c.containerid = csr.containerid
                        LEFT JOIN container cc ON cc.containerid = c.containerid
                        LEFT JOIN uom u ON u.uomid = cc.uomid
                        LEFT JOIN apsplandetail ap ON ap.containerid = c.containerid AND ap.workflowstepid = csr.workflowstepid
                        LEFT JOIN employee e ON e.employeeid = csr.sender
                        LEFT JOIN factory f ON f.factoryid = csr.factoryid
                        LEFT JOIN userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'
                        LEFT JOIN factory ff ON ff.factoryid = csr.tofactoryid
                        LEFT JOIN userattribute uu ON uu.parentid = ff.factoryid AND uu.userattributename = '分厂代码'
                        LEFT JOIN workflowstep ws ON ws.workflowstepid = csr.workflowstepid
                        LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
                        LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
                        WHERE csr.containershiprecordid = '{0}'  
                  </string>.Value, containerShipRecordID)
            Dim dtShipCon = DatabaseHelper.Query_ReturnDatTable(sql) '查询转工批次相关数据
            If dtShipCon.Rows.Count = 0 Then
                message = "不存在该转工，请重新查询"
                Return False
            End If
            '-------------赋值批次相关数据------------'
            Dim drShipCon = dtShipCon.Rows(0) '获取第一条即可
            Dim dic As New Dictionary(Of String, Object)
            dic.Add("bizDataNo", drShipCon("mfgordername").ToString()) '订单号
            dic.Add("batchNum", drShipCon("togetherflag").ToString()) '大批次号
            dic.Add("itemCode", drShipCon("productname").ToString()) '零件号
            dic.Add("unit", drShipCon("uomname").ToString()) '计量单位
            dic.Add("qty", drShipCon("qty").ToString()) '数量
            dic.Add("opNumber", drShipCon("specno").ToString()) '工序号
            dic.Add("transferDept", drShipCon("sendfactorycode").ToString()) '转出分厂代号
            dic.Add("transferPerson", drShipCon("sender").ToString()) '转出人
            dic.Add("rcvDept", drShipCon("receivefactorycode").ToString()) '接收分厂代号
            dic.Add("deliveryLocation", drShipCon("deliverylocation").ToString()) '接收分厂代号
            dic.Add("rdDate", If(String.IsNullOrWhiteSpace(drShipCon("starttime").ToString()), "", DateTime.Parse(drShipCon("starttime").ToString()).AddHours(-1).ToString("yyyy-MM-dd HH:mm:ss"))) '转工计划开始时间（取排产开始时间减去一个小时）
            For index = 1 To 10
                dic.Add(String.Format("userDef{0}", index.ToString().PadLeft(2, "0")), "") '预留字段
            Next
            '-------------赋值明细数据------------'
            Dim dicDetail As New List(Of Dictionary(Of String, Object))
            Dim dicDd As New Dictionary(Of String, Object)
            dicDd.Add("lineNo", 1)
            dicDd.Add("serialCode", If(drShipCon("containertype").ToString() = "7", drShipCon("containername").ToString().Replace(drShipCon("togetherflag").ToString(), ""), "")) '批次序号
            For index = 1 To 10
                dicDd.Add(String.Format("userDef{0}", index.ToString().PadLeft(2, "0")), "") '预留字段
            Next
            dicDetail.Add(dicDd)
            dic.Add("itemList", dicDetail)
            '----------插入推送表---------------'
            Dim jsonStr = JsonConvert.SerializeObject(dic) '将对象转换成JSON
            Dim insertSql As New StringBuilder
            insertSql.Append(<string>
                INSERT INTO sync_sendto_wms(json,methodname,toappid,tablename,uniquecolname,uniquecolvalue,create_emp)
                VALUES(</string>.Value)
            insertSql.AppendFormat("'{0}',", jsonStr) 'json
            insertSql.AppendFormat("'{0}',", "转工物料配送") '推送方法名
            insertSql.AppendFormat("'{0}',", toAppID_WMS) '推送目标ID
            insertSql.AppendFormat("'{0}',", "containershiprecord".ToUpper()) '推送对应表
            insertSql.AppendFormat("'{0}',", "containershiprecordid".ToUpper()) '推送对应表列明
            insertSql.AppendFormat("'{0}',", drShipCon("containershiprecordid").ToString()) '推送对应表列值
            insertSql.AppendFormat("'{0}')", m_UserInfo.EmployeeID) '创建人
            DatabaseHelper.ExecuteSql(insertSql.ToString())
            Return True
        Catch ex As Exception
            message = String.Format("推送WMS失败，原因：{0}", ex.Message)
            Return False
        End Try
    End Function

    ''' <summary>
    ''' 将转工完成信息汇总后保存到WMS推送表 add by zhangrj 20251014
    ''' </summary>
    ''' <param name="containerShipRecordID"></param>
    ''' <param name="m_UserInfo"></param>
    ''' <param name="message"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SendContainerShipFinishDataToWMS(ByVal containerShipRecordID As String, ByVal m_UserInfo As CurrentUserInfo, ByRef message As String) As Boolean Implements uLMSContainerShipIBusiness.SendContainerShipFinishDataToWMS
        Try
            Dim sql = String.Format(<string>
                        SELECT c.mfgordername,c.containername,c.togetherflag,c.productname,s.specno,u.uomname,
                        c.qty,ap.starttime,c.containertype,csr.containershiprecordid,
                        u.attributevalue sendfactorycode,csr.deliverylocation
                        FROM containershiprecord csr
                        LEFT JOIN containercurrentinfo c ON c.containerid = csr.containerid
                        LEFT JOIN container cc ON cc.containerid = c.containerid
                        LEFT JOIN uom u ON u.uomid = cc.uomid
                        LEFT JOIN apsplandetail ap ON ap.containerid = c.containerid AND ap.workflowstepid = csr.workflowstepid
                        LEFT JOIN factory f ON f.factoryid = csr.factoryid
                        LEFT JOIN userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'
                        LEFT JOIN workflowstep ws ON ws.workflowstepid = csr.workflowstepid
                        LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
                        LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
                        WHERE csr.containershiprecordid = '{0}'  
                  </string>.Value, containerShipRecordID)
            Dim dtShipCon = DatabaseHelper.Query_ReturnDatTable(sql) '查询转工批次相关数据
            If dtShipCon.Rows.Count = 0 Then
                message = "不存在该转工，请重新查询"
                Return False
            End If
            '-------------赋值批次相关数据------------'
            Dim drShipCon = dtShipCon.Rows(0) '获取第一条即可
            Dim dic As New Dictionary(Of String, Object)
            dic.Add("bizDataNo", drShipCon("mfgordername").ToString()) '订单号
            dic.Add("batchNum", drShipCon("togetherflag").ToString()) '大批次号
            dic.Add("itemCode", drShipCon("productname").ToString()) '零件号
            dic.Add("unit", drShipCon("uomname").ToString()) '计量单位
            dic.Add("qty", drShipCon("qty").ToString()) '数量
            dic.Add("opNumber", drShipCon("specno").ToString()) '工序号
            dic.Add("transferDept", m_UserInfo.FactoryCode) '转出分厂代号（反过来了，转出分厂取值转工接收部门）
            dic.Add("transferPerson", m_UserInfo.FullName) '转出人（反过来了，转出人取值转工完成人）
            dic.Add("rcvDept", drShipCon("sendfactorycode").ToString()) '接收分厂代号（取值转工转出部门）
            dic.Add("deliveryLocation", drShipCon("deliverylocation").ToString()) '接收分厂代号
            dic.Add("rdDate", If(String.IsNullOrWhiteSpace(drShipCon("starttime").ToString()), "", DateTime.Parse(drShipCon("starttime").ToString()).AddHours(-1).ToString("yyyy-MM-dd HH:mm:ss"))) '转工计划开始时间（取排产开始时间减去一个小时）
            For index = 1 To 10
                dic.Add(String.Format("userDef{0}", index.ToString().PadLeft(2, "0")), "") '预留字段
            Next
            '-------------赋值明细数据------------'
            Dim dicDetail As New List(Of Dictionary(Of String, Object))
            Dim dicDd As New Dictionary(Of String, Object)
            dicDd.Add("lineNo", 1)
            dicDd.Add("serialCode", If(drShipCon("containertype").ToString() = "7", drShipCon("containername").ToString().Replace(drShipCon("togetherflag").ToString(), ""), "")) '批次序号
            dicDd.Add("AcceptanceQty", "") '合格数
            dicDd.Add("LossQty", "") '报废数
            dicDd.Add("LossReason", "") '报废原因
            dicDd.Add("ReworkQty", "") '返修数
            dicDd.Add("DefectQty", "") '超差数
            dicDd.Add("NonconformingNo", "") '不合格审理单号
            dicDd.Add("ScrapNo", "") '报废单号
            For index = 1 To 10
                dicDd.Add(String.Format("userDef{0}", index.ToString().PadLeft(2, "0")), "") '预留字段
            Next
            dicDetail.Add(dicDd)
            dic.Add("itemList", dicDetail)
            '----------插入推送表---------------'
            Dim jsonStr = JsonConvert.SerializeObject(dic) '将对象转换成JSON
            Dim insertSql As New StringBuilder
            insertSql.Append(<string>
                INSERT INTO sync_sendto_wms(json,methodname,toappid,tablename,uniquecolname,uniquecolvalue,create_emp)
                VALUES(</string>.Value)
            insertSql.AppendFormat("'{0}',", jsonStr) 'json
            insertSql.AppendFormat("'{0}',", "转工物料配送完成") '推送方法名
            insertSql.AppendFormat("'{0}',", toAppID_WMS) '推送目标ID
            insertSql.AppendFormat("'{0}',", "containershiprecord".ToUpper()) '推送对应表
            insertSql.AppendFormat("'{0}',", "containershiprecordid".ToUpper()) '推送对应表列明
            insertSql.AppendFormat("'{0}',", drShipCon("containershiprecordid").ToString()) '推送对应表列值
            insertSql.AppendFormat("'{0}')", m_UserInfo.EmployeeID) '创建人
            DatabaseHelper.ExecuteSql(insertSql.ToString())
            Return True
        Catch ex As Exception
            message = String.Format("推送WMS失败，原因：{0}", ex.Message)
            Return False
        End Try
    End Function

    ''' <summary>
    ''' 将转工撤销信息汇总后保存到WMS推送表 add by zhangrj 20250918
    ''' </summary>
    ''' <param name="containerShipRecordID"></param>
    ''' <param name="m_UserInfo"></param>
    ''' <param name="message"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SendContainerShipCancelDataToWMS(ByVal containerShipRecordID As String, ByVal m_UserInfo As CurrentUserInfo, ByRef message As String) As Boolean Implements uLMSContainerShipIBusiness.SendContainerShipCancelDataToWMS
        Try
            Dim sql = String.Format(<string>
                        SELECT c.mfgordername,c.containername,c.togetherflag,c.productname,s.specno,u.uomname,
                        c.qty,c.containertype,csr.containershiprecordid
                        FROM containershiprecord csr
                        LEFT JOIN containercurrentinfo c ON c.containerid = csr.containerid
                        LEFT JOIN container cc ON cc.containerid = c.containerid
                        LEFT JOIN uom u ON u.uomid = cc.uomid
                        LEFT JOIN workflowstep ws ON ws.workflowstepid = csr.workflowstepid
                        LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
                        LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
                        WHERE csr.containershiprecordid = '{0}'  
                  </string>.Value, containerShipRecordID)
            Dim dtShipCon = DatabaseHelper.Query_ReturnDatTable(sql) '查询转工批次相关数据
            If dtShipCon.Rows.Count = 0 Then
                message = "不存在该转工，请重新查询"
                Return False
            End If
            '-------------赋值批次相关数据------------'
            Dim drShipCon = dtShipCon.Rows(0) '获取第一条即可
            Dim dic As New Dictionary(Of String, Object)
            dic.Add("bizDataNo", drShipCon("mfgordername").ToString()) '订单号
            dic.Add("batchNum", drShipCon("togetherflag").ToString()) '大批次号
            dic.Add("itemCode", drShipCon("productname").ToString()) '零件号
            dic.Add("unit", drShipCon("uomname").ToString()) '计量单位
            dic.Add("qty", drShipCon("qty").ToString()) '数量
            dic.Add("opNumber", drShipCon("specno").ToString()) '工序号
            dic.Add("transferDept", m_UserInfo.FactoryCode) '拒收分厂代号
            dic.Add("transferPerson", m_UserInfo.FullName) '拒收人
            For index = 1 To 10
                dic.Add(String.Format("userDef{0}", index.ToString().PadLeft(2, "0")), "") '预留字段
            Next
            '-------------赋值明细数据------------'
            Dim dicDetail As New List(Of Dictionary(Of String, Object))
            Dim dicDd As New Dictionary(Of String, Object)
            dicDd.Add("lineNo", 1)
            dicDd.Add("serialCode", If(drShipCon("containertype").ToString() = "7", drShipCon("containername").ToString().Replace(drShipCon("togetherflag").ToString(), ""), "")) '批次序号
            For index = 1 To 10
                dicDd.Add(String.Format("userDef{0}", index.ToString().PadLeft(2, "0")), "") '预留字段
            Next
            dicDetail.Add(dicDd)
            dic.Add("itemList", dicDetail)
            '----------插入推送表---------------'
            Dim jsonStr = JsonConvert.SerializeObject(dic) '将对象转换成JSON
            Dim insertSql As New StringBuilder
            insertSql.Append(<string>
                INSERT INTO sync_sendto_wms(json,methodname,toappid,tablename,uniquecolname,uniquecolvalue,create_emp)
                VALUES(</string>.Value)
            insertSql.AppendFormat("'{0}',", jsonStr) 'json
            insertSql.AppendFormat("'{0}',", "转工物料配送撤销") '推送方法名
            insertSql.AppendFormat("'{0}',", toAppID_WMS) '推送目标ID
            insertSql.AppendFormat("'{0}',", "containershiprecord".ToUpper()) '推送对应表
            insertSql.AppendFormat("'{0}',", "containershiprecordid".ToUpper()) '推送对应表列明
            insertSql.AppendFormat("'{0}',", drShipCon("containershiprecordid").ToString()) '推送对应表列值
            insertSql.AppendFormat("'{0}')", m_UserInfo.EmployeeID) '创建人
            DatabaseHelper.ExecuteSql(insertSql.ToString())
            Return True
        Catch ex As Exception
            message = String.Format("推送WMS失败，原因：{0}", ex.Message)
            Return False
        End Try
    End Function
#End Region

End Class
